Forum Discussion
Repeating Rank values in the RANX dax
Hi,
i am using RANKX measure to create rank index to my [devitaion in pcs] measure which is using two tables to calculated dviation. Froma Actuals & Forecast table . this is the measure i am using
| Actual month | Supplier location | Ship-To Location | LGC | Purchase FC | Actuals | Deviation in Pcs | FC Acuuracy in % | Root cause | Contra actions | Rank |
| 8/1/2024 | a | c1 | ab123 | 12040 | 127120 | 115080 | -856% | NA | NA | 1 |
| 8/1/2024 | a | c1 | ab231 | 42880 | 98865 | 55985 | -31% | NA | NA | 2 |
| 7/1/2024 | a | b1 | ab563 | 0 | 54060 | 54060 | NA | NA | NA | 2 |
| 7/1/2024 | a | b1 | ab765 | 0 | 53555 | 53555 | NA | NA | NA | 2 |
| 9/1/2024 | a | a1 | abg54 | 57760 | 107130 | 49370 | 15% | NA | NA | 2 |
| 7/1/2024 | a | b1 | ab231 | 0 | 45250 | 45250 | NA | NA | NA | 2 |
How do i get unique rank values to my table.
5 Replies
When you use the RANKX function in DAX to create a ranking measure, repeating rank values can occur when there are ties in the values being ranked. In your case, it seems that the [LAG 1 FC Deviation in Pcs] measure is resulting in equal values for multiple rows, which leads to the same rank being assigned.
To address the issue of repeating ranks, you can modify your RANKX measure to use a tiebreaker. A common approach is to add a secondary column to the ranking criteria that ensures uniqueness among the tied items.
insandur , Try using DAX
RANKX(
ALLSELECTED(
Actuals[Ex Works Date],
Actuals[Location Desc],
Actuals[Ship-To Location Desc],
Actuals[LGC ID],
Actuals[LGC Description],
Actuals[Qty]
),
[LAG 1 FC Deviation in Pcs] + Actuals[UniqueID] * 1e-10,
,
DESC
)Try this
Rank =
RANKX(
ALLSELECTED(
Actuals[Ex Works Date],
Actuals[Location Desc],
Actuals[Ship-To Location Desc],
Actuals[LGC ID],
Actuals[LGC Description],
Actuals[Qty]
),
[LAG 1 FC Deviation in Pcs] +
DIVIDE(
Actuals[Qty],
MAXX(ALLSELECTED(Actuals), Actuals[Qty]),
0
),
,
DESC
)
Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!Thank You Saud,
I tried above method, i get below error on this.
Hi insandur ,
Is it possible to provide .pbix files without sensitive data? It looks like your '[LAG 1 FC Deviation in Pcs]' returned a duplicate value, so the subsequent rank returned is 2.
Best Regards,
Wenbin Zhou
