Forum Discussion
kirbynguyen
Helper II
Helper IIRanking Per Row in a Matrix
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!
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])) ) )
1 Reply
- Fowmy
Super User
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])) ) )