TOP N with same values

[Anonymous]

Hi, I have a problem, in my report I have one table, and I should show the top 10 of the partner by count incident:

I had thought in the visual filter of the table, use the TOP 10 filter in the field of ShorPartnerName by the metric "Count of Total inc":

But this way dont work like I would like because the table show more than 10 ShortPartnerName, I undertand that the reason is the repeated values in the metric of "Count of Total inc".

Is there a way to only show only the top 10 in descending order even though there are repeating values?

Thanks.

[amitchandak]

@Anonymous , you need to use a tiebreaker. Add a very small random number and rank on that.

https://databear.com/how-to-use-the-dax-rankx-function-in-power-bi/

https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns

https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415

[Anonymous]

Sorry @amitchandak , Im very new in PowerBi and Im very lost. In this capture, ShortPartnerName is a field and Count Of Total Inc is this measure:

"Count of Total Inc" = DISTINCTCOUNT(vw_GBL_MotorIncident[IncidentDetailsID])+0

I don´t know if I have to creaete a field or measure and how I should do it, please, could you give more details? thanks

[PhilWinston · Accepted solution]

@Anonymous You need to create a tie breaker field so all results with the same ranking can be ranked further. So if you have 2 results with the same value (let's say "1") you need to give them a value after the decimal related to how you want to further rank them.

EG: To break the tie alphabetically you need to create a ranking of ShortPartnerName and then divide by 10,100,1000,10000 etc depending on how many you have (if you have less than 100 then divide by 100 so the result will be less than 1, if you have 10000 or if you think it will grow to that many then divide by 10000). You should now have the original rank as an integer value and the alphabetical rank as as a decimal that's less than 1. If you then create a field adding them together, all values of 1 from the original rank will now be 1.01, 1.02 etc and you can use that field as your TopN filter.

[Anonymous]

@PhilWinstonGreat, I did it following your indication and is solved, thanks a lot