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]))

1 Comment

  1. 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)

Leave a Reply

Your email address will not be published.

*

clear formPost comment