Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi - looking for some help with a RankX DAX calculation. For illustrative purposes, I'm using a simple table with Category, SubCategory, and Value. I'd like to create a measure that ranks by Category for total value across all SubCategory.
Currently, the measure is:
Solved! Go to Solution.
A measure like this should work
CategoryRank = RANKX(ALL('Table'[Category]), CALCULATE(SUM('Table'[Value]), ALLEXCEPT('Table','Table'[Category])))
Hi @brentmav ,
According to your description, my understanding is that you want to rank your data by category and subcategory in a measure. If anything is misunderstood, please tell me.
Would you please create a measure to determine whether it is a Category or SubCategory?
ISCategory = COUNTROWS('Table') = CALCULATE(COUNTROWS('Table'),ALL('Table'), VALUES('Table'[Category]))
Then create a measure for ranking:
CategoryRank = IF( [ISCategory], RANKX( ALLSELECTED('Table'[Category]),CALCULATE(SUM('Table'[Value]))), RANKX(ALLSELECTED('Table'[SubCategory]),CALCULATE(SUM('Table'[Value]))))
For more details, please refer to https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/Ee2raMYlhUJEk0MhAd...
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Hi,
I am not sure of what you want but see if my solution in this file helps.
Ashish Sir,
I also need this solution for my office assigment please share the DAX formula. In my assinnments there area 5 categoriues like 2 Regions, 7 Zones, 24 Branches and 45 Area Sales Officers, i want to making ranking from between Retions, then Zones, then brnahes then area sales officers... please guide.
Hi,
I do not have that file now. Share some data and show the expected result.
Dear Sir,
Reference data is attached.
Thank You so much for support.
https://drive.google.com/file/d/18ULxB7JdQXjqQszXQHw9BQ28lazJsD5X/view?usp=sharing
Share the download link of Excel and/or PBI file and show the expected result clearly.
Dear Sir,
Reference data is attached.
Thank You so much for support.
https://drive.google.com/file/d/18ULxB7JdQXjqQszXQHw9BQ28lazJsD5X/view?usp=sharing
A measure like this should work
CategoryRank = RANKX(ALL('Table'[Category]), CALCULATE(SUM('Table'[Value]), ALLEXCEPT('Table','Table'[Category])))
Thank you very much! Definitely wasted too much time trying to figure this one out!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |