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
AddedRunningSumHi 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!
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
