Forum Discussion
RankX Measure ties affecting Pareto graph.
Hi All
I am currently trying to build a Pareto graph that dynamically changes based on filter selections. I have created 3 measures to do this.
The first measure Ranks the Data as Follows.
Rank = IF(HASONEVALUE('Table'[Group]),
RANKX(ALLSELECTED('Table'[Group]),
CALCULATE(SUM('Table'[Duration]), ALLSELECTED('DateTable')),,DESC))
The second measure calculates the cumulative Sum of the Duration based on the rank as follows
Cumulative Duration = VAR XY=[Rank]
RETURN( CALCULATE(
SUM('Table'[Duration]),
FILTER(ALLSELECTED('Table'[Booking Group] ),
[Rank]<= XY
)))I then built a pareto based on these by [Cumulative Duration]/[Total Duration]
This method works fine until theres a point in the data where two Groups have the same Duration. This results in a flat line(As shown below) in the Pareto graph since the Durations ar the same and the duration is not cumulative in this instance.
Flat lined pareto
Can anyone help with breaking these ties to ensure that the pareto graph works as it should.
Thanks in advance
Update: I have managed to solve the case of ties. To do this, I created a unique ID column in my data and then added the (Minimum of the ID)/Large Number to the rank calculation. So if two items are ranked 5 and one item had a Minimum ID of 2 and the second one had a Minimum of 4 I then took the ID and divided by 100 to get 0.02 and 0.04. So the ranks can be broken. The table below explains this:
Value Rank Min of ID Min ID/100 New Value New rank 100 1 3 0.03 100.03 1 12 2 9 0.09 12.09 2 11 3 5 0.05 11.05 3 10 4 7 0.07 10.07 5 10 4 8 0.08 10.08 4 7 5 1 0.01 7.01 6 So the New rank would then be:
Rank = IF(HASONEVALUE('Table'[Group]), RANKX(ALLSELECTED('Table'[Group]), CALCULATE(SUM('Table'[Duration]), ALLSELECTED('DateTable'))+INT(CALCULATE(MIN('Table'[ID])))/100,,DESC))Hope this logic is clear for future queries.
This solution was derived from a method shown on the site below:
http://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
2 Replies
Hi MV13,
Please create another measure to test using the formula below, I create another measure named "Test of Rank", it should returen the max value utill current rows. Please add the [Rank] and [Test of Rank] measure in a table to compare the result.Test of rank=VAR XY=[Rank] RETURN(YY)
If the result is right, while the duration is still not cumulative, please share your .pbix file for further analysis.
Best Regards,
AngeliaUpdate: I have managed to solve the case of ties. To do this, I created a unique ID column in my data and then added the (Minimum of the ID)/Large Number to the rank calculation. So if two items are ranked 5 and one item had a Minimum ID of 2 and the second one had a Minimum of 4 I then took the ID and divided by 100 to get 0.02 and 0.04. So the ranks can be broken. The table below explains this:
Value Rank Min of ID Min ID/100 New Value New rank 100 1 3 0.03 100.03 1 12 2 9 0.09 12.09 2 11 3 5 0.05 11.05 3 10 4 7 0.07 10.07 5 10 4 8 0.08 10.08 4 7 5 1 0.01 7.01 6 So the New rank would then be:
Rank = IF(HASONEVALUE('Table'[Group]), RANKX(ALLSELECTED('Table'[Group]), CALCULATE(SUM('Table'[Duration]), ALLSELECTED('DateTable'))+INT(CALCULATE(MIN('Table'[ID])))/100,,DESC))Hope this logic is clear for future queries.
This solution was derived from a method shown on the site below:
http://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
