Forum Discussion
Rank based on category column
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 create a measure with below formula, then add it to the same table visual. This measure ranks Category based on Sum of Amount descendingly.
Rank = RANKX(ALLSELECTED('Table'),CALCULATE(SUM('Table'[Amount]),ALLEXCEPT('Table','Table'[Category])),,DESC,Dense)
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
For the data mentioned by you, the rank should be 1 for 100 , 2 for 30 ... Then again it should be 1 for 40 and 2 for 23. Then again it should be 1 for 93 and 2 for 80
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.