Top N percent filter

[bo_afk]

Hi,

Is it possible to filter values based on a top n percentage?

Suppose I have 50 categories of items, I would like to see the top 20% of categoies by sales.

i.e. the top 10 categories with highest sales.

Number of categories can change so top n categories can also change with the same percentage figure. Anyone know if this can be done?

Thanks

afk

[Karen1025]

Hi v-xicai, what if my rank is a measure instead of column? there is an error message 'Column in table cannot be found or may not be used in this expression

[v-xicai]

Hi  @bo_afk ,

Does that make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case.

Best regards

Amy Cai

[v-xicai · Accepted solution]

Hi,

You can create column Rank, then create measure Filter1 , and put Filter1 in Visual Level filter of visual displaying the Categories, setting Filter1 as "is not blank".

Rank = RANKX(Table1,Table1[sales],,DESC,Dense)

Filter1 = IF(MAX(Table1[Rank])<=0.2*COUNT(Table1[categories ]),1,BLANK())

Here is my test pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EUHUXFGQTd5PpFqMWv...

Best Regards,

Amy

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

[makcim392]

in filter:

Filter1 = IF(MAX(Table1[Rank])<=02*COUNT(Table1[categories ]),1,BLANK())

shoulnd't it be 0.2 instead of 02?

[TomMartens]

Hey,

please provide a pbix file that contains some sample data and represents your data model.

Upload the file to onedrive or dropbox and share the link.

Regards,

Tom