Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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?
Solved! Go to 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.
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.
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.
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.
Hi, Thank you for solutions to table and matrix visual. However, when I tried to create another visual from the main table, I need this to visual to be interactive when I click any of the value from the demo you have provided. Could you please help to create the table which can interact with another visual from the main table?