RANKX with duplicate values

[NLewis]

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

[v-luwang-msft · Accepted solution]

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]

@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]

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

[NLewis]

THANKS! 🙂 It works...

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

[Ashish_Mathur]

Hi,

Does this measure work?

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

Hope this helps.

[NLewis]

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

[NLewis]

https://www.dropbox.com/s/wfbleuyf31h96j8/Rank%20Again.pbix?dl=0

Here is a copy of my file (a clean version). I've tried so many different ways...I really give up...I've been at this for 4 months...maybe I need to go back to school for coding...or I'll just have to keep adjusting in Excel...this is unreal

I really do appreciate all your help

[NLewis]

I attached a copy of a clean version that I need to rank...you would not believe how long...and how many different ways....I'm 3 months in...I'm at my wits end...or I'll just keep adjusting it in Excel...or go back to school for coding...lol

https://www.dropbox.com/s/wfbleuyf31h96j8/Rank%20Again.pbix?dl=0

And thanks again 🙂 

[goncalogeraldes]

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]

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

[Syndicate_Admin]

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

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

[goncalogeraldes]

@Syndicate_Admin nice signature name, I like it!

[goncalogeraldes]

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