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

        )

pankajnamekar25's avatar
pankajnamekar25
Super User
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
    Super User
    10 months ago

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

    • lherbert501's avatar
      lherbert501
      Post Prodigy
      10 months ago

      Hi lbendlin ,

       

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

       

      Thanks

       

      • lbendlin's avatar
        lbendlin
        Super User
        10 months ago

        VAR rankSales =

            RANKX (

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

                [Total Spend]+RAND()/1000,

                ,

                DESC

            )