cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
waseem24 Helper I
Helper I

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

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

 

 

5 REPLIES 5
Super User II
Super User II

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

Hi @waseem24 

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

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Super User IV
Super User IV

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

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





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blog -Week is not so Weak Proud to be a Super User! Connect on Linkedin


Microsoft
Microsoft

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

@waseem24 

 

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.

waseem24 Helper I
Helper I

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

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

 

waseem24 Helper I
Helper I

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

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

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors