Filter top N % based on value

[Anonymous]

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

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[v-stephen-msft · Accepted 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.

[amitchandak]

@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