RANKX tie breaking / Running Total Calculations

[OB_Local]

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!

[parry2k]

@OB_Local in this post there is  a section on breaking ties, hope it helps.

[OB_Local]

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!

@parry2k 

[v-yuta-msft]

@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