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

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
Community 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