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

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