Forum Discussion

NLewis's avatar
NLewis
Frequent Visitor
4 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()...
Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    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

goncalogeraldes's avatar
goncalogeraldes
Super User
4 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
4 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
    Super User
    4 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

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    4 years ago

    Hi @NLewis, try this then!

    1. Create a duplicate or reference to the fact table in Power Query
    2. Remove all columns except the value and attribute columns
    3. Sort by descending value and attribute ascending
    4. Add an index column (from 1)
    5. Create a connection from the leaderboard to the model fact table (many to one)
    6. create a range measure in the report view with the following formula and use it as a classification system

    goncalogeraldes_2-1631263182248.png

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

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

    I hope this answer solves your problem! If you need additional help, please tag me in your response.
    If my answer provided you with a solution, mark it as a solution ✔️ or give it a kudoe 👍
    Thank you!

    Best regards
    Gonçalo Geraldes