Forum Discussion

lherbert501's avatar
lherbert501
Post Prodigy
10 months ago
Solved

Duplicate ranking

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 (   ...
Need Help
  • pankajnamekar25's avatar
    pankajnamekar25
    10 months ago

    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's avatar
    lbendlin
    10 months ago

    VAR rankSales =

        RANKX (

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

            [Total Spend]+RAND()/1000,

            ,

            DESC

        )

lbendlin's avatar
lbendlin
Super User
10 months ago

VAR rankSales =

    RANKX (

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

        [Total Spend]+RAND()/1000,

        ,

        DESC

    )

lherbert501's avatar
lherbert501
Post Prodigy
9 months ago

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?