Forum Discussion
Solved
Rank based on category column
I have three columns Category , subcategory and Amount column. I am adding category , subcategory and sum of amount column in table. I want to calculate rank (sum of amount column) based on only Ca...
Sorry I think this measure should be helpful.
Rank = RANKX(ALLSELECTED('Table'[SubCat]),CALCULATE(SUM('Table'[Amount])))Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
You can use this measure
Rank = RANKX(ALLEXCEPT('Table','Table'[Category]),CALCULATE(SUM('Table'[Amount])),,DESC,Dense)
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
I have added sample data below. With your measure i am getting below rank in visual. I have added what is required in last column.
Rank = RANKX(ALLEXCEPT(Sheet1,Sheet1[Category]),CALCULATE(SUM(Sheet1[Amount])),,DESC,Dense)
| Category | SubCat | Amount |
| AAA | AAA1 | 1 |
| AAA | AAA1 | 3 |
| AAA | AAA1 | 4 |
| AAA | AAA2 | 2 |
| AAA | AAA2 | 6 |
| AAA | AAA2 | 7 |
| BBB | BBB1 | 3 |
| BBB | BBB1 | 4 |
| BBB | BBB1 | 5 |
| BBB | BBB1 | 6 |
| BBB | BBB1 | 7 |
| BBB | BBB2 | 8 |
| BBB | BBB2 | 9 |
| BBB | BBB2 | 10 |
| BBB | BBB2 | 11 |
| CCC | CCC1 | 12 |
| CCC | CCC2 | 13 |
| CCC | CCC2 | 14 |
| CCC | CCC3 | 15 |
| CCC | CCC3 | 16 |
Sorry I think this measure should be helpful.
Rank = RANKX(ALLSELECTED('Table'[SubCat]),CALCULATE(SUM('Table'[Amount])))Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.You are a lifesaver. Thank you..What if I want to add some filters to this ??
Which kind of filters? Can you provide some examples?