Forum Discussion
Rank with the same numbers
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?
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.
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)))
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.