Top N percent filter

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

TomMartens
Super User
7 years ago
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
v-xicai
Community Support
7 years ago
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/EUHUXFGQTd5PpFqMWvQjingBM8NnGABYMZpMRzCELD_3bA?e=bhCE3k

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
  Advocate I
  6 years ago
  in filter:
  
  Filter1 = IF(MAX(Table1[Rank])<=02*COUNT(Table1[categories ]),1,BLANK())
  
  shoulnd't it be 0.2 instead of 02?
v-xicai
Community Support
7 years ago
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
Anonymous
Not applicable
4 years ago
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

Forum Discussion