Forum Discussion

OB_Local's avatar
OB_Local
Frequent Visitor
7 years ago

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

    • OB_Local's avatar
      OB_Local
      Frequent 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!

       

      parry2k 

      • v-yuta-msft's avatar
        v-yuta-msft
        Icon for Community Support rankCommunity 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