cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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
Community Support

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.

9 REPLIES 9
Community Support

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.

Resolver II

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

Community Support

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.

Resolver II

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

Community Support

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.

Resolver II

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

Community Support

Which kind of filters? Can you provide some examples?

Resolver II

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.

Community Support

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.

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors