Hesitates with the TopN 10 formula when there are many rows with the maximum value

[Syndicate_Admin]

Hello dear community, I have a question when I use the Dax topn formula, I want to get the top 10 operations with greater effectiveness, but the dax does not work as it should, the problem is that there are more than 10 operations that have the maximum value of effectiveness (100%), which dax can I use or what can I do so that BI only grabs me 10

[Syndicate_Admin]

Hello @gerardo212, Good day
What I have used in some cases and it has helped me is to put a filter at the visual object level
Kind of

You drag the field you want to filter
Under Filter type you select TopN
Then you indicate the amount you want to see, in your case it would be 10
And finally you indicate according to which value you want to see (Effective Operations)

I hope it helps you, Greetings

[Syndicate_Admin]

I tried that and it didn't work either, I think it's because of the % effectiveness it's a metric it's not a column of a table

[v-rzhou-msft · Accepted solution]

Hi @Syndicate_Admin ,

According to your statement, I think your issue is that there may be more than 10 category has the maximum value of effectiveness (100%), so the Top10 will return more than 10 results in your visual.

As far as I know, this is by design. Due to these category has the same top1 value so PBI will show all their data.

If you want to only show 10 data, I think you can try rank() function to create measure.

Firstly, you can create a rank for your percentage, and then you need to create a rank for the product or category name. Then combine both rank by rank2 * 10000( this should larger than the counts of your data) + rank1.

Finally show Top10 data of it.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.