RANK function - How to handle ties

Hello ​@pigment_enthusiast ,

Can you elaborate on how you did this rank? If you use the RANK function, you can use the MINIMUM parameter to rank similar values as the same rank output. This will give you the same position for all the “20” values.

 

<<Minimum>> is still not working.
I have a source metric: with dimensions team and Year
for every year and team.

target metric is of same dimensions: for each year, i need to show only the Top 3 Teams.
This isn’t working out as expected.

IF(RANK('team count by month',Month, DESC,MINIMUM) > 0 AND RANK('team count by month', Month, DESC,MINIMUM) < 4, 'team count by month')

Can you share a screenshot of what you see and what is not expected in the result? This might help to pinpoint the issue.

IF(RANK('team count by month',Month, DESC,MINIMUM) > 0 AND RANK('team count by month', Month, DESC,MINIMUM) < 4, 'team count by month')
Target metric should also show the team with 17%

Trying to replicate your case, but I need more details what isn’t expected on your side because it seems to work on my side with a replication of your formula. 

This is the image of the target metric

Previously attached image is of source metric

So, you actually need the first 4 values. So your formula should do >= 4 instead of >4. 
Also, you could make this 4 value dynamic, so it counts how many first places you have with a formula like this. 

 

IF(RANK('source data',Year, DESC,MAXIMUM) > 0 AND 
RANK('source data', Year, DESC,MAXIMUM) <= ((IF(((RANK('source data',Year, DESC,minimum))) = 1 , 1,blank)[remove: Company]) +1)
, 'source data')

But i need to see only top 3 records (including the ties) 
E.g. A 20
B 20
C 20
D 5 
E 5
F 7
My formula should return all of them for nov because there are ties among them. and all are in top 3.

Can you please explain why are we considering equals to 4 as well?

The standard RANK function behaviour is that it will skip places for ties. 
An example down here. You can also see it when you just execute the rank, without the if statement. 

I’ll have a think about how we can adress this. 

Got it, so basically it assigns the same rank but creates the gap.
Please let me know if there’s any solution to this issue.
 

Hi ​@Stef ,
Is there any alternative solution to this issue?

Hi ​@pigment_enthusiast , 

Thanks for your patience. At this moment I can’t find a clean way of getting the behaviour that you desire with formulae. 
I would suggest posting the idea to have a RANK without a “gap” as an idea in here so our Product team can consider it for the roadmap.

Thanks @Stef for your response.
Sure, I have posted it as an idea. 

Hi ​@pigment_enthusiast ,

This is an interesting extension of the Rank feature—keep this idea! In the meantime, I could have an alternative solution:

Assume this is the source : 
 

so its rank native should be like this : 
 

Then we create a dimension Order with auto generate unique IDs until the last rank number: 
 

Then create 3 metrics step by step :
Tech. Rank01 : ITEM(RANK('Source data', Month,DESC ), Order.Name)
Tech. Rank02 : CUMULATE(('Tech. Rank01'[BY COUNT: 'Tech. Rank01', Month]-1), Order)
Tech. Rank03 : IF( 'Tech. Rank01' = Order."1", 'Tech. Rank01'.Name, ('Tech. Rank01'.Name - 'Tech. Rank02'[BY CONSTANT: Order - 1])[FILTER: Order = 'Tech. Rank01'][REMOVE FIRSTNONBLANK: Order])

The goal is to eliminate redundant sorting.

Here is the result :

Hoping this is helpful for you.
Weining