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 (

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

        [Total Spend],

        ,

        DESC

    )

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

        )

8 Replies

  • 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

         

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    10 months ago

    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.

  • v-tejrama's avatar
    v-tejrama
    Community Support
    10 months ago

    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's avatar
      v-tejrama
      Community Support
      10 months ago

      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.