Counting similar values on different columns to create a matrix

[Anonymous]

It is the first question I have ever posted here. I spend a long time trying to find an answer; however, I haven't got anything yet. It is an easy question, but I really stuck with it. I would appreciate it if anyone could help me.

I have a rank 1, 2, and 3 of products (A, B, C) in their different columns as in the table below;

Post Area	Rank 1	Rank 2	Rank 3
E	A	C	B
N	C	B	A
SE	B	A	C
NW	B	A	C
SW	C	B	A
W	C	B	A
RM	A	B	C
IG	A	C	B
CR	C	B	A
HA	B	C	A
EN	B	A	C
UB	B	A	C
DA	B	A	C
TW	B	C	A
B	B	C	A
LU	A	B	C

I need to count the number of products in each column and create a matrix on my dashboard such as this.

	Rank 1 Count	Rank 2 Count	Rank 3 Count
A	4	5	7
C	4	5	7
B	8	6	2

How can I achieve this task in pbi?

[Anonymous]

Thank you very much for your answer, @v-jingzhang, but the rank columns are part of a big table and result from measures. However, I created a rank table in PBI to follow your instruction, but it looks created tables are not being transformed in the power query.

[v-jingzhang · Accepted solution]

Hi @Anonymous 

Yes, a calculated table is in the model, so it cannot be seen in Power Query Editor. But based on your calculated table, you can create measures to get the count results. I have created a sample file attached at bottom. Hope it is helpful. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Anonymous]

Thanks a lot, @v-jingzhang; this worked for the case.

[v-jingzhang]

Hi @Anonymous 

The current table format is not friendly for a matrix visual, so you need to transform it in Power Query Editor first. Click "Transform data" to open Power Query Editor, select Rank1, Rank2, Rank3 columns (hold on Shift key to select multiple columns), right click on the column header and select "Unpivot Columns" option.

You will get the following table. Click "Close & Apply" to apply this table to Power BI Desktop.

Then add a matrix visual. Use "Value" on Rows, "Attribute" on Columns, "Post Area" on Values with "Count" aggregation type. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.