Rank with the same numbers

[Paula]

Hello! i'm having some problems whit the function RANKX.

What can i do if i have the same amount in different categories and i want to do a rank? i want to see only the top 5, but the rank 5 is 11 times, so how can i choose some of them? 

Example:

Thanx!

[v-jiascu-msft]

Hi @Paula,

Which some of them do you want? Are there any other conditions? If it's possible to sort Amount in the Query Editor and add an index?

Best Regards,

Dale

[Paula]

Hi @v-jiascu-msft

I have no preference. If I add an index, can I choose one of the categories and obtain the top 5? In case of adding an index, what would the DAX be like?

There is no other condition.

[Greg_Deckler]

Try specifying Dense as an option, the default is Skip.

https://msdn.microsoft.com/en-us/query-bi/dax/rankx-function-dax

[Paula]

Hi @Greg_Deckler

i'm using Dense, but the rank is the same when i have the same amount for different categories.

[Greg_Deckler]

OK, so my understanding is that you want the top 5 categories. But, some of the categories have the same amount that you are ranking on. I guess my question is, how do you want it to work? In your scenario where you have a bunch of ranking "5", do you want it to randomly pick one? What is your expectation?

[Paula]

Hi @Greg_Deckler

Yes!, i want to choose one at random. Is that possible?

[Greg_Deckler]

Possibly, you would want to use the RAND function to generate a random number. Alternatively, you could just use MAX or MIN on the category to grab the first or last one.

[Paula]

@Greg_Deckler

In which part of the function should i use RAND?

The function that i'm using is this:

Rank = (RANKX(FILTER('Tabla1'; 'Tabla1'[Id Periodo] = EARLIER('Tabla1'[Id Periodo]))); RANKX(ALL('Tabla1'); 'Tabla1'[Monto]; ;DESC;Dense)))

[Greg_Deckler]

Well, that's going to be kind of interesting. You would need to create a second column/measure probably that looks at your rankings and identifies duplicates. You would use something like COUNTROWS for all rankings equal to EARLIER ranking. Then, if that is greater than 1, you would generate a random number using RAND between 1 and that number. Then you would assign that row the rank value and everything else would get +1 that rank. Going to get tricky though because if it is, say, rank 2 and you want the top 5 then all other ranks have to get pushed down. Might be better to go with SKIP in stead of DENSE in your RANKX in this case so that it isn't so much of a chore.

Can you post your data in text for so that it can be copied and pasted? I don't feel like typing all of that data.