Filter by cumulative percent / bad performance

[Drew89]

I need to calculate the cumulative percent of sales amount by product and then set a report wide filter to only show items that are in the top N percent of total sales.  

I'm running into a few issues.  First it doesn't appear that I can use a measure as a report or page level filter in the filter pane nor can I use it as a slicer.  I can't create a calculated column to store the cumulative percent because the users can filter/slice by different dimensions which would change the total sales (I need the cumulative percent to be dynamic).  Is my only option to use a filter in the filter pane at the visual level?  What If I have 5+ pages with 4-5 visuals per page...then I have to put this filter on 20+ visuals and have my users adjust the filter that many times?

The second issue is performance.  I have a test report using the Contoso model and I have a set of working measures that I wrote but the performance is terrible.  On the Contoso model it takes around 1.5 seconds but on my real model it takes 25+ seconds.  The Contoso model has 100,000 rows of sales data and 2,500 products and my real model has 4,500,000 rows of sales data and 275,000 products. You can download the file from here, https://www.dropbox.com/scl/fi/1w2xmkkxiibsx7n7twgqm/07-02-Cumulative-total-on-customer-class.pbix?r... (it is a modified version of the PBIX from this SQLBI article).

Below are my measures

Product Rank

Product Rank = 
RANKX(
    ALLSELECTED('Product'[Product Name]),
    Sales[Sales Amount], , 
    DESC,
    DENSE
)

Sales Amount Rolling Total By Product

Sales Amount RT Product = 
CALCULATE(
    [Sales Amount],
    WINDOW(
        1, ABS,
        0, REL,
        SUMMARIZE( ALLSELECTED( Sales ), 'Product'[Product Name] ),
        ORDERBY ( [Product Rank] )
    )
)

Cumulative Percent 

Cumulative Percent = 
DIVIDE(
    [Sales Amount RT Product],
    [Total Sales Amount],
    0
)

Products 20th Percentile

Products 20th Percentile = 
IF(
    [Cumulative Percent] <= 0.20,
    1,
    0
)

[Anonymous]

Hi, @Drew89 

I failed to open your link. I gave an example about using the filter pane.

Measure 2 = IF([Measure]<=0.2,1,0)

You can use measure in the filter pane like this.

Regarding the second question, you can check the link hopefully it will help you.

Optimizing DAX expressions involving multiple measures - SQLBI

BLAZING FAST DAX QUERIES | HOW TO OPTIMIZE SLOW POWER BI REPORTS AND DAX QUERIES (youtube.com)

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Drew89]

Hmm.  I was able to download it using an incognito tab in Chrome.  Dropbox is a little weird because it acts like it needs you to sign up to download a file but you can click on "Or continue with download only" to get the file without signing up or logging in.

I know you can apply a measure as a filter at the visual level.  I would like to apply the filter at the report level or as a synced slicer so that I only have to make the change in one place.  Say I have 5 pages in my report and I have 5 visuals per page.  I would have to apply the visual level filter 25 times to filter the report.  Then if I want to make changes to the filter, I have to make changes on each visual's filter pane.

Is there not a way to use a measure and filter the entire report based on that measure?