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

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