Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
darshaningale
Resolver II
Resolver II

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 Category and should not change by addition of subcategory.

1 ACCEPTED SOLUTION

Hi @darshaningale 

 

Sorry I think this measure should be helpful.

Rank = RANKX(ALLSELECTED('Table'[SubCat]),CALCULATE(SUM('Table'[Amount])))

vjingzhang_0-1688717980429.png

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

View solution in original post

9 REPLIES 9
v-jingzhang
Community Support
Community Support

Hi @darshaningale 

 

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)

vjingzhang_0-1688367213766.png

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

Hi @darshaningale 

 

You can use this measure

Rank = RANKX(ALLEXCEPT('Table','Table'[Category]),CALCULATE(SUM('Table'[Amount])),,DESC,Dense)

vjingzhang_0-1688694686182.png

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)
 

 

 

darshaningale_0-1688708861155.png

 

CategorySubCatAmount
AAAAAA11
AAAAAA13
AAAAAA14
AAAAAA22
AAAAAA26
AAAAAA27
BBBBBB13
BBBBBB14
BBBBBB15
BBBBBB16
BBBBBB17
BBBBBB28
BBBBBB29
BBBBBB210
BBBBBB211
CCCCCC112
CCCCCC213
CCCCCC214
CCCCCC315
CCCCCC316

 

 

 

Hi @darshaningale 

 

Sorry I think this measure should be helpful.

Rank = RANKX(ALLSELECTED('Table'[SubCat]),CALCULATE(SUM('Table'[Amount])))

vjingzhang_0-1688717980429.png

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?

Lets assume the table has more columns such as "Type" where values are High , Critical , Low...And i want to select only High values while calculating the rank.

I add a slicer to filter the "Type", the measure still works when the columns in the table visual are not changed. See the attached sample file below. 

Helpful resources

Announcements
October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.