cancel
Showing results for
Did you mean: New Member

## 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.

 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
1 ACCEPTED SOLUTION  Community Support

Hi @lucaslp ,

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 3  Super User

Hi,

Hope this helps. Regards,
Ashish Mathur
http://www.ashishmathur.com  Community Support

Hi @lucaslp ,

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.  Super User

@lucaslp , Refer if this can help, Top N percentile

Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!   