# Power BI Exchange

View Only

## Cumulative Total by Group

• #### 1.  Cumulative Total by Group

Posted Sep 22, 2020 03:10 AM
Hi,
I am adding a new column using DAX to calculate a running total for each group. Could somebody help me understand why my formula doesn't work as intended?

I have copied the formula and the data table from this discussion:

RunningTotal = CALCULATE( SUM('Input Table'[Value]), FILTER( ALLEXCEPT('Input Table', 'Input Table'[Project]), 'Input Table'[Period]<=MAX('Input Table'[Period]) ) )​

Thank you,

------------------------------
Saulius G
------------------------------

• #### 2.  RE: Cumulative Total by Group

Top Contributor
Posted Sep 22, 2020 03:18 AM
Try to create Measure instead of Column

------------------------------
Farhan Ahmed
karachi
3452523688
------------------------------

• #### 3.  RE: Cumulative Total by Group

Posted Sep 22, 2020 03:33 AM
Hi Farhan,

Thanks for the quick reply. I need this to be at individual row level, as I will then do additional operations with the cumulative total. See the full sample table. I need to evaluate whether Project-Period combination will have enough inventory, considering that it is drawing from total inventory at a Project level.

Thanks,

------------------------------
Saulius G
------------------------------

• #### 4.  RE: Cumulative Total by Group

Top Contributor
Posted Sep 22, 2020 03:50 AM
Then use EARLIER instead of MAX in your codeRunningTotal = CALCULATE( SUM('Input Table'[Value]), FILTER( ALLEXCEPT('Input Table', 'Input Table'[Project]), 'Input Table'[Period]<=EARLIER('Input Table'[Period]) ) )​​

------------------------------
Farhan Ahmed
karachi
3452523688
------------------------------

• #### 5.  RE: Cumulative Total by Group

Posted Sep 22, 2020 04:07 AM
Perfect, thank you!

Is there any reason why I should use 'filter'?
It seems to give the same result both with and without this function.

------------------------------
Saulius G
------------------------------

• #### 6.  RE: Cumulative Total by Group

Top Contributor
Posted Sep 22, 2020 05:11 AM
When you use single-column reference to filter the data without using FILTER it converts the code internally to the FILTER context. However, you cannot write a single filter argument referencing two different columns

Better detail is provided by @Marco Russo under the article " Filter Arguments"

Ref: https://www.sqlbi.com/articles/filter-arguments-in-calculate/
​​

------------------------------
Farhan Ahmed
karachi
3452523688
------------------------------

• #### 7.  RE: Cumulative Total by Group

Posted Sep 22, 2020 11:44 AM
This is great. Thank you very much Farhan. All is working now.

------------------------------
Saulius G
------------------------------

• #### 8.  RE: Cumulative Total by Group

Posted Sep 23, 2020 03:56 PM
Edited by Saulius G Sep 23, 2020 03:58 PM
Hi again,

I ran into a "There is not enough memory to complete this operation" error when running this for 28K rows (it did work for 7K rows).
Is there a way to make my running total formula more efficient by using variables?

I am pretty sure that above formula is the issue (I have all columns reduced to bare minimum, disabled 'enable load' for all tables that are not required for reporting in query editor, increased Data Cache Management Options to 31MB, etc).

I was reading below source, which says that "You should be using variables(Var) for storing results in case of complex calculations"
https://community.powerbi.com/t5/Desktop/There-is-not-enough-memory-to-complete-this-operation/td-p/688511

Thanks again,

------------------------------
Saulius G
------------------------------

• #### 9.  RE: Cumulative Total by Group

Posted Oct 10, 2020 10:50 AM
In case anybody stumbles on this topic - I tried this as both a measure and a calculated column. Both would run out of memory for 30K+ rows.

In the end I have moved this to PowerQuery, following advice below.
Video 1 for running total (simple, not by group):
Video 2 for grouped running total

This was my code, but video explains is really well:
...
#"Sorted Rows - Part&Order" = Table.Sort(#"Removed Other Columns1",{{"PART_NO", Order.Descending}, {"ORDER_NO", Order.Ascending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows - Part&Order", {"PART_NO"}, {{"Count", each _, type table [ORDER_NO=nullable text, PART_NO=nullable text, Qty_Remaining=nullable number]}}),

//Function to calculate running totals

RunFunction = (RunTable as table) as table=>
let
in

//Call the function

RunTotals = Table.TransformColumns( #"Grouped Rows", {"Count", each RunFunction(_)}),
#"Expanded Count" = Table.ExpandTableColumn(RunTotals, "Count", {"ORDER_NO", "Qty_Remaining", "Custom"}, {"ORDER_NO", "Qty_Remaining", "Custom"}),

#"Renamed Columns" = Table.RenameColumns(#"Expanded Count",{{"Custom", "RunningTotal"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Qty_Remaining", type number}, {"RunningTotal", type number}}),
...

------------------------------
Saulius G
------------------------------

• #### 10.  RE: Cumulative Total by Group

Posted Jan 24, 2022 02:04 PM
Avoid EARLIER on large datasets.  Microsoft warns it's memory use is exponential because it returns an entire dataset for each row processed.  For example, processing 100 rows could result in 10,000 entries in memory.  Processing 38,000 could potentially result in 38K x 38K.

https://docs.microsoft.com/en-us/dax/earlier-function-dax

------------------------------
James Thomas
------------------------------