Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
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
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?
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.