Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I have data that when put in a Matrix looks like this (keep in mind the data isn't just one row per value, there are multiple rows per combination, so each value in each cell is an aggregation):
Apple | Banana | Celery | Donuts | Earth | Fork | |
A | 11 | 32 | 18 | 25 | 41 | 30 |
B | 24 | 8 | 34 | 28 | 21 | 49 |
C | 31 | 10 | 32 | 11 | 22 | 25 |
D | 15 | 31 | 48 | 25 | 20 | 28 |
E | 47 | 44 | 19 | 21 | 37 | 38 |
F | 32 | 43 | 30 | 35 | 36 | 17 |
What I want is to rank each value by row from highest to lowest, so the matrix i would like to have would be:
Apple | Banana | Celery | Donuts | Earth | Fork | |
A | 6 | 2 | 5 | 4 | 1 | 3 |
B | 4 | 6 | 2 | 3 | 5 | 1 |
C | 2 | 6 | 1 | 5 | 4 | 3 |
D | 6 | 2 | 1 | 4 | 5 | 3 |
E | 1 | 2 | 6 | 5 | 4 | 3 |
F | 4 | 1 | 5 | 3 | 2 | 6 |
Data would look something like this:
Date | Name | Category | Value |
1/1/2021 | A | Apple | 10 |
1/1/2021 | A | Banana | 20 |
1/1/2021 | A | Celery | 9 |
1/1/2021 | A | Donuts | 20 |
1/1/2021 | A | Earth | 35 |
1/1/2021 | A | Fork | 22 |
1/1/2021 | A | Apple | 1 |
1/2/2021 | A | Apple | 0 |
So, the values in the matrix are aggregations of the rows. The date doesn't matter in this context.
Please help. Thanks in advance!
Solved! Go to Solution.
@kirbynguyen
You can use the following measure to get the desired ranking.
Ranking =
IF(
HASONEVALUE(Table2[Category]) && HASONEVALUE(Table2[Name]),
RANKX(
ALLSELECTED(Table2[Category]),
CALCULATE(SUM(Table2[Value]))
)
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@kirbynguyen
You can use the following measure to get the desired ranking.
Ranking =
IF(
HASONEVALUE(Table2[Category]) && HASONEVALUE(Table2[Name]),
RANKX(
ALLSELECTED(Table2[Category]),
CALCULATE(SUM(Table2[Value]))
)
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
User | Count |
---|---|
94 | |
86 | |
78 | |
69 | |
63 |
User | Count |
---|---|
113 | |
99 | |
97 | |
64 | |
59 |