Duplicate ranking

[lherbert501]

Hi,

What's the best way to avoid duplicate rankings for total spend with the measure below?

Rank =

VAR selectedMetric =

    SELECTEDVALUE ( Slicer[Value] )

VAR rankSales =

    RANKX (

        ALLSELECTED ( table1[field1], table1[field2], table1[field3] ),

        [Total Spend],

        ,

        DESC

    )

[pankajnamekar25 · Accepted solution]

Hello @lherbert501 

Try this DAX 

If you have a unique field (like CustomerID, StoreID, etc.), include it in the expression:

Rank =
VAR selectedMetric =
SELECTEDVALUE(Slicer[Value])
RETURN
RANKX(
ALLSELECTED(table1[field1], table1[field2], table1[field3]),
[Total Spend] * 1000000 + table1[UniqueID], // tie-breaker
,
DESC
)

If my response helped you, please consider clicking
Accept as Solution ✅ and giving it a Like 👍 – it helps others in the community too.


Thanks,


Connect with me on:

LinkedIn

[v-tejrama]

Hi @lherbert501 ,

Thank you @pankajnamekar25  for the response provided!

Has your issue been resolved? If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

Thank you for your understanding!

[v-tejrama]

Hi @lherbert501 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[Ashish_Mathur]

Hi,

In the orderby argument of the RANK() function, use the ORDERBY() function and in there you can specify as many columns as you want to order by (it can include a combination of measures and columns).  To receive specific help, share some data to work with.  Share data in a format that can be pasted in an MS Excel file.

[pankajnamekar25 · Accepted solution]

Hello @lherbert501 

Try this DAX 

If you have a unique field (like CustomerID, StoreID, etc.), include it in the expression:

Rank =
VAR selectedMetric =
SELECTEDVALUE(Slicer[Value])
RETURN
RANKX(
ALLSELECTED(table1[field1], table1[field2], table1[field3]),
[Total Spend] * 1000000 + table1[UniqueID], // tie-breaker
,
DESC
)

If my response helped you, please consider clicking
Accept as Solution ✅ and giving it a Like 👍 – it helps others in the community too.


Thanks,


Connect with me on:

LinkedIn

[lbendlin]

another alternative is to add a small, insignificant random number to the values before you try the ranking.

[lherbert501]

Hi @lbendlin ,

How would this be done? Its just a simple count of records but the number being ranked is single digit

Thanks

[lbendlin · Accepted solution]

VAR rankSales =

    RANKX (

        ALLSELECTED ( table1[field1], table1[field2], table1[field3] ),

        [Total Spend]+RAND()/1000,

        ,

        DESC

    )

[lherbert501]

@lbendlin I have in my example top 5 all have the value of 1 thus top 5 have ranking of 1. This can't be altered with a small, insignificant random number. Is there another way?