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.
I know how to use the Top N filter, but the ask is for the top 10% of values. We have a large dataset of tickets, each ticket has a "duration" value. We want to see the 10% of those values in a table
Any help on how to filter that is appreciated
Solved! Go to Solution.
@bhmiller89
Top 10% of the tickets and their duration.
Top 10% Duration =
VAR T10 =
TOPN(
ROUNDUP(
COUNTROWS(ALL('Table'))*0.1,0),
ALL('Table'),
[Total Duration],
DESC
)
RETURN
CALCULATE(
[Total Duration],
KEEPFILTERS(T10)
)
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon on the right if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@bhmiller89
Top 10% of the tickets and their duration.
Top 10% Duration =
VAR T10 =
TOPN(
ROUNDUP(
COUNTROWS(ALL('Table'))*0.1,0),
ALL('Table'),
[Total Duration],
DESC
)
RETURN
CALCULATE(
[Total Duration],
KEEPFILTERS(T10)
)
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon on the right if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
You could try something like this:
Top 10% Customer =
VAR Num =
INT ( COUNTROWS ( Customer ) * 0.1 )
VAR Top10Customer =
TOPN ( Num, Customer, [Total Sales], ASC )
RETURN
Top10Customer
@bhmiller89 , refer
Top 80/20 , percent /percentile
https://blog.enterprisedna.co/implementing-80-20-logic-in-your-power-bi-analysis/
https://forum.enterprisedna.co/t/testing-the-pareto-principle-80-20-rule-in-power-bi-w-dax/459
https://finance-bi.com/power-bi-pareto-analysis/
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculate-the-sum-of-the-top-80/td-p/763156
or
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://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
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 |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |