Cumulative Totals using Multiple filter columns, Taking longer time to display the result.

[Anonymous]

Hello, 

I am trying to calculate cumulative totals based on 3 different filters, (Item, Unit, Company), I am facing a challange where in when i am trying to display the result in Table chart, It is taking too long, I am waiting for hours for this expression to evaluate.

After a long time it gives an error message stating, Not enough memory to dispay.

 I have a Ram of 16 Gb, Unable to find out what the issue is.

I am using the following Expression:

 Cumulative Total = CALCULATE(SUM(Table1[Quantity]),FILTER(ALL(Table1[Quantity],Table1[Item],Table1[Unit]),Table1[Quantity]<=MAX(Table1[Quantity])))

Thanks

[V-pazhen-msft]

@Anonymous 

CALCULATE(SUM(Table1[Quantity]),FILTER(ALL(Table1[Quantity],Table1[Item],Table1[Unit]),Table1[Quantity]<=MAX(Table1[Quantity]))) 

I don't think the bold section is written in a correct format. 

Try create a calculate column using: 

Column = CALCULATE(SUM(Table1[Quantity]),ALLEXCEPT(Table1,Table1[Item],Table1[Unit],Table1[Company]), Table1[Quantity]<=EARLIER(Table1[Quantity]))

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

[amitchandak]

I am not sure is it done in the right manner. Can you share sample data and sample output.

[Anonymous]

Hi,

@amitchandak 

As we can see in the .pbix file, I have Branch, Item, Sum Item Wise(Sum of quantity item wise sorted in descending order), Here I have calculated contribution measure which is the value to the percentage of Sum item wise to the grand totals, 

I want to have a cumulative totals of the Measure Contribution which should be dynamic, when i change filter of item and branch it should calcuate and display the cumulative totals.

I have created a measure as rolling contribution but it doesn't give the required results.

https://drive.google.com/file/d/1dxwmA-6bKscJiZZKWDGZwmTgErbDnqWA/view?usp=sharing

[az38]

Hi @Anonymous 

didnt you try a measure like?

 Cumulative Total = CALCULATE(SUM(Table1[Quantity]),ALLEXCEPT(Table1,Table1[Item],Table1[Unit],Table1[Company]), Table1[Quantity]<=MAX(Table1[Quantity])))

[Anonymous]

Yes, I have tried using this DAX as well, but it doesnt give me the required results.