Dynamic count of values based on selections/slicer

[Katalo]

Im quite new to Power BI, but Im getting the hang of it. Im building a report based on interview asnwers and got stuck on problem and I hope someone here could give me a push in the right direction.

I have a tabel of interview answers, with columns for each question and rows for each respondent. The respondent also answers question about for example age, gender and that sort of things. 

For most questions the respondent answer on a scale from 1-5, and I would like to create a measure that counts all the occurances of 1 and 2. I want to be able to select the questions in a slicer och a chart will show how many has answered 1 and 2 and split that by the total amount of values in the column (to get a percentage).

I fugured i cound use count and filter, but I can only filter by a specific column (question) and i would like to be able to change the coulmn but count values in the same way. Above that i would be nice to be able to filter the respondents by age and so on. 

Here in an example of what the data looks like, its about 100 questions and 10 000 rows in the original

Respondent	response_date	age	gender	Q1	Q2	Q3	Q4	Q5	Q6	…
51321351	2018-01-01	18	m	4	4	3	2	5	1	2
51321514	2018-01-02	33	f		3		5	1	2	3
51321677	2018-01-03	45	m	2	1	2		4	5
51321840	2018-01-04	48	m	5	4	3	2	5	1	2
51322003	2018-01-05	52	m	1	3	2	5		2	3
51322166	2018-01-06	19	f	2		2	3	4	5	4
51322329	2018-01-07	36	f	3	4	3	2	5	1
51322492	2018-01-08	74	m		3	2	5	1	2	3
51322655	2018-01-09	62	f	5	1	2	3		5	4
….

Thanks in advance!

[Thejeswar · Accepted solution]

@v-chuncz-msft,

Is this what you mean? (See pic. below)

The Above chart shows the no. of times a value occurs for each question.

To get this use Unpivot option in the Power Query

The Following Screenshot shows the same

After importing the data, Select all the non- Question Columns and then Under Transform Menu of the Ribbon, click on "Unpivot Other Columns" Option

 After importing the source data into Power BI, as shown in the above pic, click the Unpivot Other Columns option under the Transform Menu of Power Query after selecting all the non-Question Columns

After Unpivoting the other columns, the data will look like as shown below

Now build the first posted graph using this table.

[v-chuncz-msft]

@Katalo,

You may select Unpivot Columns in Query Editor first.

https://community.powerbi.com/t5/Desktop/matrix-to-table-help/td-p/329415

[Anonymous]

Maybe you could upload sample data set for better understanding?