Display in table/matrix visual Count of number of occurrences of value in multiple columns

[Nickb167]

Hi,

Fairly new to PowerBi and a bit stuck on something that is probably fairly trivial. I have a data set from a SQL source that is essentially 1-5 ratings on a number of questions and it looks like the below:

Column1	Column2	Column3	Column4	Column5
4	4	3	4	3
4	5	4	5	4
2	4	3	1	4
3	3	2	3	4
3	4	4	4	3
3	3	5	3	5
4	1	2	2	3
4	3	3	4	4
2	1	1	1	3
1	5	1	1	1
3	4	4	3	3
2	2	1	1	1
1	3	2	2	3
1	4	1	1	2
3	3	2	2	3
5	5	3	2	5
1	5	5	2	2
3	1	1	3	3
4	5	3	4	4

I need to display this in a table or matrix visual similar to below. 1 occurs 4 times in Column1, 2 occurs 3 times in Column1, 3 occurs 6 times in Colunm1m and so on.

	Column1	Column2	Column3	Column4	Column5
1	4	3	5	5	2
2	3	1	4	5	2
3	6	5	5	4	8
4	5	5	3	4	5
5	1	5	2	1	2

I've tried various things and can't seem to get anything to work. Any help would be appreciated!

[Nickb167]

Thanks for your help!!

[amitchandak]

@Nickb167 , Check file after Signature

1. Added index column. (Power Query)

2. Unpivoted -https://youtu.be/2HjkBtxSM0g

3. Created Rank Table, Enter Data

[amitchandak]

@Nickb167 , On the way, is to unpivot The table and then join it Rank (1-5, Need to there on Table)

https://radacad.com/pivot-and-unpivot-with-power-bi

And count the new column and pivot in Matrix

Another way is joining Rank(1-5, Need to there on Table) Table with All five columns, One active others inactive. Now create count measure with userelation

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

[Greg_Deckler · Accepted solution]

@Nickb167 - See the attached PBIX below sig, Page 22. So in Power Query select all your columns, right-click Unpivot. Then see matrix layout on Page 22.

[Greg_Deckler]

@Nickb167 - You need to unpivot your columns, PBIX coming.