Rank based on category column

[darshaningale]

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.

[v-jingzhang]

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)

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

[darshaningale]

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

[v-jingzhang]

Hi @darshaningale 

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.

[darshaningale]

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

[v-jingzhang · Accepted solution]

Hi @darshaningale 

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.

[darshaningale]

You are a lifesaver. Thank you..What if I want to add some filters to this ??

[v-jingzhang]

Which kind of filters? Can you provide some examples?

[darshaningale]

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.

[v-jingzhang]

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.