Running totals in Power Query, using the List.Range function
The List.Range M language function can create running totals - when each row is a summary of it an all rows above.
(To calculate running total in DAX, click here.)
Lets use this table (it must have the Index column):
Use custom column to define the numbers to summarize:
- =List.Range(
Table_with_index[Price],
0,
[Index])
Because:
- =List.Range(
Table_with_index[Price], - na name of previous step and column used for summarizing
0, - the start is from first item (Power Query calculate from 0, not from 1...)
[Index]) - we will get as many numbers from Price, as defined in Index column
Then simply switch the datatype of result column to number:
and group it, using Sum:
Thats all:
Nice, but isn't it too long?
If you are not afraid of M language, you can use List.Accumulate and do it all in one formula.
The thing is, that List.Range finds the area to calculate, and then List.Accumulate summarize it.
- List.Accumulate(
List.Range(
#"Changed Type"[Price],
0,
[Index]),
0,
(state, current)=>state + current)
Similarly, you can use List.Sum:
- List.Sum(
List.Range(
#"Changed Type"[Price],
0,
[Index]))
The List>sum option works well for me. But how would you reset the running total on change of animal if you had for example a date field for when they were sold. You want the running total for Hamster ( 2 + 6 + 9), then reset to zero for a running to for Gerbil (1 + 2 + 4)