Forum Discussion
RankX Measure ties affecting Pareto graph.
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
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