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,
I'm trying to filter categories (here provenance of visitors) based on the top 80% of values (here volume of visits). See example below.
To be more precise, I'd like to see the top provenances for which the sum of visits is <=80%. In my example the sum of A + B +C +D + E = 77%.
So that means I'd like to only see provenances A to E displayed in the results. The end goal is to have a map filtered to show only the top (circa) 80% of provenance.
Many thanks for your help
Provenance of visitors | volume of visits |
A | 24 |
B | 19 |
C | 14 |
D | 11 |
E | 9 |
F | 6 |
G | 5 |
H | 3 |
I | 2 |
J | 2 |
K | 1 |
L | 1 |
M | 1 |
N | 1 |
O | 1 |
Solved! Go to Solution.
Hi @Anonymous ,
1.Create a calculated column to get the cumulative value.
Cumulative value =
CALCULATE (
SUM ( 'Table'[volume of visits] ),
FILTER (
'Table',
[Provenance of visitors] <= EARLIER ( 'Table'[Provenance of visitors] )
)
)
2.Create a measure and put it into Filters. Set show items when the value is 1.
Measure =
VAR sumvisit =
CALCULATE ( SUM ( 'Table'[volume of visits] ), ALL ( 'Table' ) )
RETURN
IF ( MAX ( 'Table'[Cumulative value] ) <= sumvisit * 0.8, 1 )
You can check more details from here.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi @Anonymous ,
1.Create a calculated column to get the cumulative value.
Cumulative value =
CALCULATE (
SUM ( 'Table'[volume of visits] ),
FILTER (
'Table',
[Provenance of visitors] <= EARLIER ( 'Table'[Provenance of visitors] )
)
)
2.Create a measure and put it into Filters. Set show items when the value is 1.
Measure =
VAR sumvisit =
CALCULATE ( SUM ( 'Table'[volume of visits] ), ALL ( 'Table' ) )
RETURN
IF ( MAX ( 'Table'[Cumulative value] ) <= sumvisit * 0.8, 1 )
You can check more details from here.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , Refer if this can help, Top N 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
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 |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |