Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Filter top N % based on value

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 visitorsvolume of visits
A24
B19
C14
D11
E9
F6
G5
H3
I2
J2
K1
L1
M1
N1
O1
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

3 Replies