Repeating Rank values in the RANX dax

[insandur]

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

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],,DESC).   But i get repeating rank values except for 1st rank value.

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.

[v-zhouwen-msft]

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

[Kedar_Pande]

@insandur 

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.

[saud968]

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!

[insandur]

Thank You Saud,

I tried above method, i get below error on this.

[bhanu_gautam]

@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
)