topic Re: Number of Items Making up Top X% of Sales - Measure Very Slow in DAX Commands and Tips

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

Brotedo — Fri, 16 Sep 2022 15:12:00 GMT

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 ) )

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

daXtreme — Fri, 16 Sep 2022 19:37:22 GMT

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

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

v-yueyunzh-msft — Mon, 19 Sep 2022 02:29:30 GMT

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