Running Sum by group for several columns with values

[Poloscopie]

Hello everyone,

I'm stuck with an issue which I could partially fix with the solution find here.

My data show three different values for each day (deploy, needs, resources) for each combo snapshot/reference/factory.

I could manage to get a nice cumulative view for one value (the deploy in my example), but I'd like to get the running sum for each type of quantity.

Here's what I have for now:

Data without cumulative values:

With the following function and the steps declined below, I was able to get the running sum for deploy quantities.

fnAddRunningSum

(MyTable as table) as table =>
let
  Source = Table.Buffer(MyTable),
  TableType = Value.Type(Table.AddColumn(Source, "CumulativeDeploy", each null, type number)),
  Cumulative = List.Accumulate(Source[DEP],{},(cumulative,qty) => cumulative & {List.Last(cumulative, 0) + qty}),
  AddedRunningSum = Table.FromColumns(Table.ToColumns(Source)&{Cumulative},TableType)
in
  AddedRunningSum

Steps

let
    //Source = EditedTypes
    TableTypeCumDeploy = Value.Type(Table.AddColumn(Source, "CumulativeDeploy", each null, type number)),
    TableTypeCumNeeds = Value.Type(Table.AddColumn(Source, "CumulativeNeeds", each null, type number)),
    TableTypeCumResources = Value.Type(Table.AddColumn(Source, "CumulativeResources", each null, type number)),
    #"Grouped_Rows_Deploy" = Table.Group(#"EditedTypes", {"SNAPSHOT", "REF", "FACTORY"}, {{"AllData", fnAddRunningSum, TableTypeCumDeploy}}),
    #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped_Rows_Deploy", "AllData", {"PROJ_DATE", "DEP", "CumulativeDeploy", "NEED", "CumulativeNeeds", "RESS", "CumulativeResources"})
in
    #"Expanded AllData"

Result:

Now I'd like to fill the two other cumulative values (selected columns in the screenshot), but I don't know how to proceed…

I'm clearly stuck, as I began working with Power Query only a few weeks ago. I'd be really grateful to anyone who finds out the solution here.

[v-jingzhang]

Hi @Poloscopie 

If your PROJ_DATE column doesn't have duplicated values for each combo snapshot/reference/factory, you can add a custom column with below code. Click fx icon beside formula bar to enter this code. Change previousStepName accordingly.

= Table.AddColumn(previousStepName, "Cumulative DEP", each List.Sum(Table.SelectRows(previousStepName, (x)=> x[SNAPSHOT]=[SNAPSHOT] and x[REF]=[REF] and x[FACTORY]=[FACTORY] and x[PROJ_DATE]<=[PROJ_DATE])[DEP]))

Modify above code for NEED and RESS. 

= Table.AddColumn(previousStepName, "Cumulative NEED", each List.Sum(Table.SelectRows(previousStepName, (x)=> x[SNAPSHOT]=[SNAPSHOT] and x[REF]=[REF] and x[FACTORY]=[FACTORY] and x[PROJ_DATE]<=[PROJ_DATE])[NEED]))

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Poloscopie]

Hello @v-jingzhang!

Thanks a lot for your answer.

In theory, the solution works, but as I'm dealing with a huge number of lines (> 1 to 2 millions), even adding one of the three columns with your solution seems to make the calculus too long (I never made it to the end).

The solution that worked for me in my original post but that I don't know how to adapt for three columns seems to work pretty well for big amount of lines.

You're using the List.Sum function while the other solution used the List.Accumulate function. I don't if the gain of perf is due to this.

[v-jingzhang]

Hi @Poloscopie 

Yes, you are correct. My method doesn't have a good performance. If you want to use these data in a report, why not try using DAX to create measures or calculated columns to get the cumulative values? That may have a better performance. 

At present I don't understand the solution in your original post, so will need some more time to find out how to adapt it for three columns. I guess its good performance is due to Table.Buffer. But I admit that I didn't fully understand it. This may be helpful: How does Table.Buffer work? | Power Query | Excel Forum • My Online Training Hub

Jing

[Jcarofi]

Hello. 

I have a silimar problem in calculating the cumulative but using 2 arguments.
I want (Running t) taking column (ve) and the arguments are (Est) and (CICL)

[Poloscopie]

Hello Jcarofi,

It's not really similar, you've only one column of values.

But to solve your issue, the function I wrote should help you if you adapt it to your data:

fnAddRunningSum

(MyTable as table) as table =>
let
  Source = Table.Buffer(MyTable),
  TableType = Value.Type(Table.AddColumn(Source, "RunningSum", each null, type number)),
  Cumulative = List.Accumulate(Source[ve],{},(cumulative,ve) => cumulative & {List.Last(cumulative, 0) + ve}),
  AddedRunningSum = Table.FromColumns(Table.ToColumns(Source)&{Cumulative},TableType)
in
  AddedRunningSum

Steps

let
    //Source = EditedTypes
    TableType = Value.Type(Table.AddColumn(Source, "RunningSum", each null, type number)),
    #"Grouped_Rows_Deploy" = Table.Group(#"EditedTypes", {"Est", "CICL"}, {{"AllData", fnAddRunningSum, TableTypeCumDeploy}}),
    #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped_Rows_Deploy", "AllData", {"ve", "RunningSum"})
in
    #"Expanded AllData"

In my example, the source is #"EditedTypes", you have to edit it with your source name.