Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Solved! Go to Solution.
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.
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?
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
92 | |
86 | |
85 | |
66 | |
49 |
User | Count |
---|---|
140 | |
113 | |
106 | |
64 | |
60 |