Number of Items Making up Top X% of Sales - Measure Very Slow

[Brotedo]

I have a measure that returns the number of items that make up a given % of sales for a given customer. Visuals using the measure load very slowly...every other visual loads nearly instantly, and this one takes >1 minute. Any tips on ways to clean this up?

NumberOfItems = 
VAR TotalSales = CALCULATE( [NetSales], ALL( 'dw dimItem' ) )
VAR ThreesholdPercentage = 0.5
RETURN
COUNTROWS(
    FILTER(
        CALCULATETABLE(
            ADDCOLUMNS(
                ADDCOLUMNS(
                    VALUES( 'dw dimItem'[ItemKey] ),
                    "TotalSalesOuter", [NetSales]
                ),
                "CumulatedPercentage",
                DIVIDE(
                    SUMX(
                        FILTER(
                                ADDCOLUMNS(
                                    VALUES( 'dw dimItem'[ItemKey] ),
                                    "TotalSalesInner", [NetSales]
                                ),
                                [TotalSalesInner] >= [TotalSalesOuter]
                        ),
                        [TotalSalesInner]
                    ),
                    TotalSales
                )
            ),
            ALL( 'dw dimItem' )
        ),
        [CumulatedPercentage] <= ThreesholdPercentage
    )
)

[v-yueyunzh-msft · Accepted solution]

Hi, @Brotedo 

According to your description, When you put this measure in visuals, visuals become slow to load. Right?

Here are a few things you can try to optimize your dax:

(1)Filter the table as early as possible.

(2)Simplify your logic.

(3)Stop using  VALUES if you do not have to use them.

You can refer to this document : 

Performance-DAX 

Best Regards,

Aniya Zhang

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

[daXtreme]

Here is everything you need to know to write things like this: ABC classification – DAX Patterns