Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
paulomartins
Frequent Visitor

Running Total with multiple criteria

Hello guys!

For a few days I've been trying to get a DAX function that can get the same result I get from the excel formula, but unfortunately I was not successful and I come here to ask you to help and find out if it's possible.

 

I have the following accumulative sum using "Sum Ifs" function in excel. In which the sum and criteria range goes up to a row before the criterion row, as you can see:

 

Exemplo 1.png

 

Comments:

*SOMASES = SUMIFS
*In this table the dates are sorted in ascending order

 

I have the same table in Power Bi and one of the ways I tried was creating an index column to use as one of the criteria in the cumulative sum, I looked for a lot of questions similar to mine here on the forum and I realized that the answers use a function similar to I'm trying, which in this case looks like this:

 

SUM= CALCULATE(SUM(TABLE[QUANTITY]), FILTER(ALLSELECTED(TABLE),TABLE[INDEX] < EARLIER(TABLE[INDEX])))

 

Unfortunately, I'm getting a blank result and Power Bi warns that the system doesn't have enough memory for the calculation.

 

The error:
 
Exemplo 2.png
wich means: "<ccon>There is not enough memory to complete this operation. Please try again later when there may be more memory available.</ccon>"
 
Can anyone help me get a result?
1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

Hi @paulomartins 

 

Your thought is in the correct direction. You can use ALLEXCEPT function in the DAX. Try creating a calculated column with below DAX. 

Running Total = 
VAR currentIndex = 'Table'[Index]
RETURN
CALCULATE(SUM('Table'[QUANTITY]),ALLEXCEPT('Table','Table'[SKU],'Table'[DATE]),'Table'[Index]<currentIndex) + 0

vjingzhang_0-1662002817392.png

 

The "There is not enough memory to complete this operation. " error usually occurs when your computer doesn't have enough memory to perform the operation. When you use Power BI Desktop, all data in the model is loaded into memory. And creating a calculated column will add more data size to the model. If the data size is huge, you may have this error. You can turn off not used applications on the same computer to save memory space. If possible, you can consider removing unnecessay data in the model to reduce the data size. Data reduction techniques for Import modeling - Power BI | Microsoft Docs

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

View solution in original post

4 REPLIES 4
v-jingzhang
Community Support
Community Support

Hi @paulomartins 

 

Your thought is in the correct direction. You can use ALLEXCEPT function in the DAX. Try creating a calculated column with below DAX. 

Running Total = 
VAR currentIndex = 'Table'[Index]
RETURN
CALCULATE(SUM('Table'[QUANTITY]),ALLEXCEPT('Table','Table'[SKU],'Table'[DATE]),'Table'[Index]<currentIndex) + 0

vjingzhang_0-1662002817392.png

 

The "There is not enough memory to complete this operation. " error usually occurs when your computer doesn't have enough memory to perform the operation. When you use Power BI Desktop, all data in the model is loaded into memory. And creating a calculated column will add more data size to the model. If the data size is huge, you may have this error. You can turn off not used applications on the same computer to save memory space. If possible, you can consider removing unnecessay data in the model to reduce the data size. Data reduction techniques for Import modeling - Power BI | Microsoft Docs

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

Hi @v-jingzhang 
How can I modify your dax in order to pick up the value of "Quantity" with the index of 1 as my start of running total?

So the first row is 120 then 144 etc.

Appreciate your help.

 

-Third

Thank you so much for your help, unfortunely this DAX is calculating a huge data (around 12-13 milions of rows), the final solution I found was to do it in SQL inside my company datalake and let it to processing.
Again, thanks for your help, this code works when I reduce the size of data. 😄

paulomartins
Frequent Visitor

nobody? is it not posible to do with DAX? 😞

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.