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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

3 REPLIES 3
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.

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors