Forum Discussion
How to do a running Sum by group in Power Query?
You can use this query (assuming you want to group on "BU"):
let Source = Table1, TableType = Value.Type(Table.AddColumn(Source, "Running Sum", each null, type number)), #"Grouped Rows" = Table.Group(Source, {"BU"}, {{"AllData", fnAddRunningSum, TableType}}), #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Location", "Month", "Cost", "Running Sum"}, {"Location", "Month", "Cost", "Running Sum"}) in #"Expanded AllData"With function fnAddRunningSum:
(MyTable as table) as table =>
let
Source = Table.Buffer(MyTable),
TableType = Value.Type(Table.AddColumn(Source, "Running Sum", each null, type number)),
Cumulative = List.Skip(List.Accumulate(Source[Cost],{0},(cumulative,cost) => cumulative & {List.Last(cumulative) + cost})),
AddedRunningSum = Table.FromColumns(Table.ToColumns(Source)&{Cumulative},TableType)
in
AddedRunningSum
anyone?
You can use this query (assuming you want to group on "BU"):
let
Source = Table1,
TableType = Value.Type(Table.AddColumn(Source, "Running Sum", each null, type number)),
#"Grouped Rows" = Table.Group(Source, {"BU"}, {{"AllData", fnAddRunningSum, TableType}}),
#"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Location", "Month", "Cost", "Running Sum"}, {"Location", "Month", "Cost", "Running Sum"})
in
#"Expanded AllData"
With function fnAddRunningSum:
(MyTable as table) as table =>
let
Source = Table.Buffer(MyTable),
TableType = Value.Type(Table.AddColumn(Source, "Running Sum", each null, type number)),
Cumulative = List.Skip(List.Accumulate(Source[Cost],{0},(cumulative,cost) => cumulative & {List.Last(cumulative) + cost})),
AddedRunningSum = Table.FromColumns(Table.ToColumns(Source)&{Cumulative},TableType)
in
AddedRunningSum
Hi MarcelBeug,
Your solution is great!! It helps me a lot to minimize the performance issue based on DAX in the visuals!
I think it is also possible to modify the fnAddRunningSum function slightly to avoid List.Skip:
(MyTable as table) as table => let Source = Table.Buffer(MyTable), TableType = Value.Type(Table.AddColumn(Source, "Running Sum", each null, type number)), Cumulative = List.Accumulate(Source[Cost],{},(cumulative,cost) => cumulative & {List.Last(cumulative, 0) + cost}), AddedRunningSum = Table.FromColumns(Table.ToColumns(Source)&{Cumulative},TableType) in AddedRunningSumCheers!
Hello MarcelBeug , I tried using the function, what do I change if I want to group it by a different column ?
Thanks !
Hi wgjunsay ,
You may apply different "group by" column in the code (instead of "BU"):
#"Grouped Rows" = Table.Group(Source, {"BU"}, {{"AllData", fnAddRunningSum, TableType}})Cheers!
Hi people, I tried using this method and it worked, but only when I'm using group by with only one field.
Is there a way to make it work for more than one field? I want to group by name, year and month...
Hey MarcelBeug ,
I want to just do the cumulative sum without any grouping action using m query.Can you guide me through the solution for this Problem.
Hi subhashree_r ,
The actual part that does grouping in the original post was where List.Accumulate appears.
This is the function that does the actual TotalSum job and can be used without the GroupBy depends on a scenario you want to implement. There is a way to avoid grouping using custom functions in complex scenarios (where you do not need a SumTotal across the entire table), but it makes the code quite heavy to run and complex to read and understand. Why would you need it?
Kind regards,
JB
Hey MarcelBeug ,
I have got an error after applying the cumulative sum as shown below
Hi subhashree_r ,
check what are you passing to Value.Type() . Most likely the problem is there. I think if all goes well you should see "table" as an output of this step in both parts of the code.
Hi Anonymous ,
I'am not able to understand the exactly what that line (Table Type) of the code does to make the corresponding changes.The code that I have used is as given below
(MyTable as table) as table =>
let
Source = Table.Buffer(MyTable),
TableType = Value.Type(Table.AddColumn(Source, "Running Sum", each null, type number)),
Cumulative = List.Skip(List.Accumulate(Source[Cost],{0},(cumulative,cost) => cumulative & {List.Last(cumulative) + cost})),
AddedRunningSum = Table.FromColumns(Table.ToColumns(Source)&{Cumulative},TableType)
in
AddedRunningSumHi, I am using this query, thank you.
However, it seems as if the total is not per Item. The total just continue adding?
Hi to call the function when we say function is accepting two parameters?
MarcelBeug Thank you so much for the solution. You are a genius.
MarcelBeug Thanks for this, absolute lifesaver! I think I spent half a day trying to figure out how to do this on my report before stumbling upon this thread.
