cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lucaslp
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.

 

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
1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
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] )
    )
)

1.png

 

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 )

2.png

 

 

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.

View solution in original post

3 REPLIES 3
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-stephen-msft
Community Support
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] )
    )
)

1.png

 

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 )

2.png

 

 

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.

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.