Speed optimization of running total

[Anonymous]

Hello,

I would like to ask for your help with optimizing my running total measure. 

Run_total_measure = 

var q = CALCULATE( SUM ( package_item[quantity] )  )

var run_q = CALCULATE( SUM( package_item[quantity] ), FILTER( ALL( 'item'[ean], 'item'[item_name] ), CALCULATE( SUM( package_item[quantity] ) >= q ) ) )

return run_q

I need to apply this measure in Table with about 400K products (and I need it to work even for about 1M rows). But the calculation does not finish at all because of not enough memory.

Please do you see any space for optimization?

[Anonymous]

@Anonymous 
A typical Running total measure is using All/Allselected, and  [column]<=MAX([column]) as the filter expression, try the the following measure:

Run_total_measure = 

CALCULATE( SUM('package_item'[quantity] ), FILTER( ALLSELECTED( 'package_item'), 'package_item'[quantity]<=MAX('package_item'[quantity])

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello and thank you for your reply.

Syntax of the suggested measure works, but the output is not correct. Please see the link below for file with complete use case.

Thank you for your time!

[amitchandak]

@Anonymous , Try like

CALCULATE( SUM( package_item[quantity] ), FILTER( ALL( 'item'[ean], 'item'[item_name] ), package_item[quantity]= max(package_item[quantity] ) ))

[Anonymous]

Thank you for the suggestion but package_item[quantity] is a column and this syntax gives me error, bcs there are not unique values in this column

[amitchandak]

@Anonymous , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

I prepared PBIX without sensitive data. Table at the bottom, there is shown everything I need to achieve BUT it is calculated over product category (measures folder CAT).

The next step is to transfer the same logic to granularity of EAN and item_name - the table at the top (measures forder EAN). Problematic measures are ean running % of total p and ean running % of total q

https://drive.google.com/file/d/1yhpbYCI9xsH6k_3tBYNr3Tv2XdWKRwzi/view?usp=sharing