Forum Discussion
RANKX tie breaking / Running Total Calculations
This post is both for help with a problem I am facing, and a suggestion to the Microsoft team with the DAX language for the RANKX function.
Scenario: I'm creating a pareto chart, which is a chart containing a summed measure and a percentage line. I'm having issues with the DAX for the percentage line. The formula is a multi-variable equation creating a rank for my dimensions, creating a running total for the dimensions, figuring out the sum for the entire sample, then dividing the running total by the sum.
Issue: RANKX groups ties in its two tie-handling options (SKIP: 1, 2, 2, 4 vs DENSE: 1, 2, 2, 3 if you had a 4 category sample and the middle categories had the tie). When the ranked value feeds the variable which creates the running total, it handles the ties the same way, flat-lining my pareto percentage line.
I've seen the internet solutions which say to use RAND() as a tiebreaker (doesn't work when creating it all in a single measure) or dividing by a unique numeric field in my data (could lead to errors).
I'd like to know if there's a better way to create a running total based on Non-Date dimensions ; and suggest to the Microsoft team to add a new option in the final argument in the RANKX function. Maybe something called "RANDBREAK" to complement the SKIP or DENSE, to arbitrarily/randomly rank within a tie.
Thanks!
3 Replies
- parry2k
Super User
- OB_LocalFrequent Visitor
Parry,
I've seen that article, and it's a good one at that. I'd like to figure out a solution where it is not assumed there's a unique column to create an extra calculation on (if possible). I'd also like to refrain from creating a new unique calculated column to solve.
Thank you very much!
- v-yuta-msft
Community Support
OB_Local ,
I've seen that article, and it's a good one at that. I'd like to figure out a solution where it is not assumed there's a unique column to create an extra calculation on (if possible). I'd also like to refrain from creating a new unique calculated column to solve.
Could you please share some sample data and give the expected output?
Regards,
Jimmy Tao