Running Total with multiple criteria

[paulomartins]

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:

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:

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?

[v-jingzhang · Accepted solution]

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

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.

[third_hicana]

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

[paulomartins]

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]

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