Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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
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
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.
in filter:
Filter1 = IF(MAX(Table1[Rank])<=02*COUNT(Table1[categories ]),1,BLANK())
shoulnd't it be 0.2 instead of 02?
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |