Forum Discussion

NLewis's avatar
NLewis
Frequent Visitor
5 years ago
Solved

RANKX with duplicate values

Here is the Rank Measure I used for the TotalPlsRank:

TotalPlsRank = IF(HASONEVALUE(PLS_DATA[Customer (groups)]),RANKX(ALLSELECTED(PLS_DATA[Customer (groups)]),[TotalPLS],,DESC,Dense),BLANK())
It does work (as you can see above); however, I'm trying to figure a way to remove the duplicate values (multiple #1's) and base it on the TotalPieces(see below)

 Is this even possible...I can't firgure this out...I've watched countless videos and numberous webpages....still nothing

I even tried adding 2 ranks (Total Pieces Rank & Total PLS Rank)...It worked; however, it repeated when the total pieces duplicated...I'm lost....HELP....PLEASE

 

 

Need Help

13 Replies

  • v-luwang-msft's avatar
    v-luwang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi NLewis ，

    You could create the following measure:

    TOTAL = IF(HASONEVALUE(PLS_DATA[Customer]),1,0)
    Final Rank1 = 
         DIVIDE (IF(HASONEVALUE(PLS_DATA[Customer]),RANKX(ALL(PLS_DATA[Customer]),[TotalDeliveryScans],,DESC,Dense)),CALCULATE(COUNTROWS(PLS_DATA),FILTER(ALL(PLS_DATA),[TOTAL]>0) )
        
   )
    FINALRANK = IF(HASONEVALUE(PLS_DATA[Customer]),RANKX(ALL(PLS_DATA[Customer]),[PLSRanking]+[Final Rank1],,ASC,Dense))

    Final get the below :(it will rankx first base on totalpls ,if with the same totalpls ,then rank base on TotalDeliveryScans)

     

    REFER:https://community.powerbi.com/t5/Desktop/How-to-Rank-a-list-based-on-2-values-double-rankX/m-p/44008 

    Did I answer your question? Mark my post as a solution!


    Best Regards

    Lucien

    • NLewis's avatar
      NLewis
      Frequent Visitor
      4 years ago

      THANKS! 🙂 It works...

      Thanks to all that took the time to help me out!

    • NLewis's avatar
      NLewis
      Frequent Visitor
      4 years ago

      v-luwang-msft I'm absoulutly loving this formula...can i throw a FILTER(ALLSELECTED(DATES TABLE,[DATE]) somewhere...I noticed the COUNTROWS; therefore, if I select certain dates in the filter, the Rank starts at 2 (due to all rows not having data for certain dates)

      • v-luwang-msft's avatar
        v-luwang-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi NLewis ,

        In my opinion, it is possible. In my own case, I am used to using all if I don't have a slicer, and allselected if I do.

         

         

        Best Regards

        Lucien

  • goncalogeraldes's avatar
    goncalogeraldes
    Icon for Super User rankSuper User
    5 years ago

    Hello there NLewis ! I have encountered a similar problem in the past and I resorted to these link for help: 

     

    https://community.powerbi.com/t5/Desktop/Drill-down-in-RANKX-on-a-matrix-visual/m-p/2061285#M769688

    https://community.powerbi.com/t5/Desktop/Rank-values-without-duplicate-ranking/m-p/702224 

    https://community.powerbi.com/t5/Desktop/Rank-distinct-values-for-ties/td-p/194301 

    https://community.powerbi.com/t5/Desktop/How-to-get-distinct-rank-based-on-duplicate-value/m-p/84362... 

     

    Hope they can help you as well!

     

    Hope this answer solves your problem! If you need any additional help please tag me in your reply.
    If my reply provided you with a solution, pleased mark it as a solution ✔️ or give it a kudoe 👍
    Thanks!

    Best regards,
    Gonçalo Geraldes

    • NLewis's avatar
      NLewis
      Frequent Visitor
      5 years ago

      None of these helped...I keep coming out with the same results...I tired breaking-ties by adding a random number to the rank...that's not working...I was also trying some variables; however, I can't seem to get this together....

       
       
      • goncalogeraldes's avatar
        goncalogeraldes
        Icon for Super User rankSuper User
        5 years ago

        Hello NLewis , try this then! 

        1. Create a duplicate or reference to your fact table in Power Query
        2. Remove all columns except the value and attribute columns and a column for linking to the fact table in the model (in my case I used the date column since I wanted an historical rank)
        3. Order the value column by descending order, and after the atribute by ascending order (keep in mind it has to be in this order value -> attribute. The value column will have a small number one besides the ordering icon and the attribute will have a number two. Check the print for info)
        4. Add an index column (starting from 1)
        5. Create a connection from the rank table to a dimension (many-to-one) table that links to the fact table in your model (one-to-many) - check image below
        6. Create a Rank measure in the report view with the following formula and use it as your ranking system

         

         

         

         

        Ranking = 
var _sales = SUM( 'Rank'[Value] )

return
IF(
  HASONEVALUE('Rank'[Attribute]), 
  RANKX(ALL('Rank'[Attribute]), 
    _sales, 
    , 
    DESC,
    Skip )
)

         

         

        Hope this answer solves your problem! If you need any additional help please tag me in your reply.
        If my reply provided you with a solution, pleased mark it as a solution ✔️ or give it a kudoe 👍
        Thanks!

        Best regards,
        Gonçalo Geraldes

  • NLewis's avatar
    NLewis
    Frequent Visitor
    5 years ago

    In my mind I'm thinking of something like this (I could be wrong)

     

    PLS_Rank =

    IF [TotalPLSRank] = [AnotherRank]

    VAR MaxTotalPieces (of the above...but total pieces are in a different column)

    Add .05 to [AnotherRank] that has the greater value

    Then take the Rank of that 


    I feel like this dont make any sense...I feel like I'm speaking another language...not english...geeeshhh

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    5 years ago

    Hi,

    Does this measure work?

    PLSRanking = 
IF(HASONEVALUE(PLS_DATA[Customer]),RANKX(ALL(PLS_DATA[Customer]),[TotalPieces],,DESC,Dense))

    Hope this helps.