Forum Discussion
Running Total for each Group / ID
Hi, I am trying to create a running total in my table that only does the running total for each respective Group / ID. An example of my table is below.
| ID | Number | Date |
| Apple | 20 | 01/01/2023 |
| Apple | -30 | 06/01/2023 |
| Apple | -40 | 11/01/2023 |
| Apple | 20 | 16/01/2023 |
| Banana | 10 | 02/12/2022 |
| Banana | 10 | 07/12/2022 |
| Banana | -40 | 12/12/2022 |
| Banana | 10 | 17/12/2022 |
| Banana | -50 | 22/12/2022 |
| Orange | -80 | 09/02/2022 |
| Orange | 10 | 21/02/2022 |
| Orange | 10 | 05/03/2022 |
| Orange | 20 | 17/03/2022 |
| Orange | 10 | 29/03/2022 |
The result would ideally look like this:
| ID | Number | Date | Running total per group |
| Apple | 20 | 01/01/2023 | 20 |
| Apple | -30 | 06/01/2023 | -10 |
| Apple | -40 | 11/01/2023 | -50 |
| Apple | 20 | 16/01/2023 | -30 |
| Banana | 10 | 02/12/2022 | 10 |
| Banana | 10 | 07/12/2022 | 20 |
| Banana | -40 | 12/12/2022 | -20 |
| Banana | 10 | 17/12/2022 | -10 |
| Banana | -50 | 22/12/2022 | -60 |
| Orange | -80 | 09/02/2022 | -80 |
| Orange | 10 | 21/02/2022 | -70 |
| Orange | 10 | 05/03/2022 | -60 |
| Orange | 20 | 17/03/2022 | -40 |
| Orange | 10 | 29/03/2022 | -30 |
I have been using these formulas in power query:
Sum by group:
= Table.AddColumn(#"Added Custom", "Custom.1", each let _Group = [ID] in
List.Sum(
Table.SelectRows(#"Added Custom", each [ID] = _Group)[Number]))
Running Total:
= Table.AddColumn(#"Added Index", "Custom", each List.Sum(List.FirstN(#"Added Index"[Number],[Index])))
I have been trying to combine these two but I cannot seem to get them to work together, does anyone have any suggestions. (I am fairly new to power query and M code).
Thank you
Use this. Replace ??? appropriately
let Source = ??? BuffNumberList = Source[Number], BuffIDList = Source[ID], GenListOfRunningTotal = List.Generate(()=>[x=BuffNumberList{0},y=0], each [y]<List.Count(BuffNumberList), each [y=[y]+1, x=if BuffIDList{y} = BuffIDList{[y]} then [x]+BuffNumberList{y} else BuffNumberList{y}], each [x]), Result = Table.FromColumns(Table.ToColumns(Source) & {GenListOfRunningTotal},Table.ColumnNames(Source)&{"Running total per group"}) in Result
3 Replies
Use this. Replace ??? appropriately
let Source = ??? BuffNumberList = Source[Number], BuffIDList = Source[ID], GenListOfRunningTotal = List.Generate(()=>[x=BuffNumberList{0},y=0], each [y]<List.Count(BuffNumberList), each [y=[y]+1, x=if BuffIDList{y} = BuffIDList{[y]} then [x]+BuffNumberList{y} else BuffNumberList{y}], each [x]), Result = Table.FromColumns(Table.ToColumns(Source) & {GenListOfRunningTotal},Table.ColumnNames(Source)&{"Running total per group"}) in ResultThank you for the response, this works great. If you don't mind, I had come up with something sort of similar as a blank query.
(values as list, ID as list) as list=>
let
RT =
List.Generate(
()=>[RT = values{0},counter = 1],
each [counter]-1 < List.Count(values),
each (if ID{[counter]-1}=ID{[counter]}
then [RT = [RT]+values{[counter]},counter=[counter]+1]
else [RT = values{[counter]},counter=[counter]+1]),
each [RT])
in
RT
However, on this line "each [counter]-1 < List.Count(values)," I get the error message: 'An error occurred in the ‘’ query. Expression.Error: There weren't enough elements in the enumeration to complete the operation.
Details: [List]', and another line is added to the table just saying error for each column.If I change the line to: " each [counter] < List.Count(values)," then the last value in the RT (running total) column is null. I am not sure of a way around this, do you have any suggestions please?
Thank you for the response, this works great. If you don't mind, I had come up with something sort of similar as a blank query.
(values as list, ID as list) as list=>
let
RT =
List.Generate(
()=>[RT = values{0},counter = 1],
each [counter]-1 < List.Count(values),
each (if ID{[counter]-1}=ID{[counter]}
then [RT = [RT]+values{[counter]},counter=[counter]+1]
else [RT = values{[counter]},counter=[counter]+1]),
each [RT])
in
RT
However, on this line "each [counter]-1 < List.Count(values)," I get the error message: 'An error occurred in the ‘’ query. Expression.Error: There weren't enough elements in the enumeration to complete the operation.
Details: [List]', and another line is added to the table just saying error for each column.If I change the line to: " each [counter] < List.Count(values)," then the last value in the RT (running total) column is null. I am not sure of a way around this, do you have any suggestions please?
