cancel
Showing results 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

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:

*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:

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
Community Support

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

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.

3 REPLIES 3
Community Support

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

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.

Frequent Visitor

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. 😄

Frequent Visitor

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### 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
Top Kudoed Authors