Running total

[scsos]

I've got some data of Incident Counts that i'm trying to create a running total for.  here's some dummy data

I've read some articles about using an Index colum and List.FirstN to do this and have tried to create a column with this

List.Sum(List.FirstN([Incidents] , [Index]))

But it gives errors on all rows

thanks

[PhilipTreacy · Accepted solution]

Hi @scsos 

You need to refer to the column of Incidents using the name of the last step.  Assuming that last step was adding the Index column, your code would look like this

= List.Sum(List.FirstN(#"Added Index"[Incidents] , [Index]))

And you need to change the Index column so that it starts at 1, not 0.  If you start it at 0 the running total will be incorrect

Regards

Phil

[MarkLaf · Accepted solution]

Edit: sanalytics is absolutely correct in their response that FirstN is a suboptimal approach. I didn't think closely enough about it initially. Updated below to use List.Generate to pass forward running total, which I believe will be most performant, which really will only be noticible if you are dealing with a non-small table (5k+ rows).

let
    Source = Sample,
    Incidents = List.Buffer(Source[Incidents]),
    Run = List.Generate(
        ()=> [ i = 0, run = List.First(Incidents) ], 
        each [i] < List.Count(Incidents), 
        each [ i = [i]+1, run = [run] + (Incidents{i}??0) ], 
        each [run]
    ),
    Combine = Table.FromColumns( 
        Table.ToColumns(Source) & { Run }, 
        type table [Month=text, Incidents=Int64.Type, Running Total=Int64.Type] 
    )
in
    Combine

Old suboptimal FirstN code for reference:

let
    Source = Sample,
    Incidents = List.Buffer( Source[Incidents] ),
    Run = List.Transform( 
        List.Positions(Incidents), 
        each List.Sum( List.FirstN( Incidents, _+1 ) ) 
    ),
    Combine = Table.FromColumns( 
        Table.ToColumns(Source) & { Run }, 
        type table [Month=text, Incidents=Int64.Type, Running Total=Int64.Type] 
    )
in
    Combine

[abdelazizallam0]

= Table.AddColumn(#"Added Index", "Custom", each List.Sum( List.FirstN( #"Reordered Columns"[Column1] , [Index])))

[sanalytics · Accepted solution]

@scsos 

Alternatively, you can use List.Accumulate for calculating running total. You dont need an extra index column for this. Below code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMU9JRMlCK1YlWcktNArKNwGzfxCI427EAxDaGilcC2YZgtlcpQq9XaQ5CfWk6kG0CZgenFsDV+yeXwM3xyy8DssFMl9RkMDMWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Incident = _t]),
    TypeChanged = Table.TransformColumnTypes(Source,{{"Month", type text}, {"Incident", Int64.Type}}),
    Result = 
    let 
varListConversion = List.ReplaceValue(TypeChanged[Incident],null,0,Replacer.ReplaceValue),
varRunningTotal  = 
Table.FromColumns(
Table.ToColumns( TypeChanged) &
{
List.Skip(List.Accumulate( 
varListConversion,
{0},
(s,c) => s & {List.Last(s)+c}
),1) }, Table.ColumnNames(TypeChanged) & {"RunningTotal"}
)
in
varRunningTotal
in
    Result

List.FirstN function is very slow function, if you are dealing with large dataset..Alternatively you can use List.Accumulate or List.Generate for that.

Hope this helps.

Regards,

sanalytics

[MarkLaf · Accepted solution]

Edit: sanalytics is absolutely correct in their response that FirstN is a suboptimal approach. I didn't think closely enough about it initially. Updated below to use List.Generate to pass forward running total, which I believe will be most performant, which really will only be noticible if you are dealing with a non-small table (5k+ rows).

let
    Source = Sample,
    Incidents = List.Buffer(Source[Incidents]),
    Run = List.Generate(
        ()=> [ i = 0, run = List.First(Incidents) ], 
        each [i] < List.Count(Incidents), 
        each [ i = [i]+1, run = [run] + (Incidents{i}??0) ], 
        each [run]
    ),
    Combine = Table.FromColumns( 
        Table.ToColumns(Source) & { Run }, 
        type table [Month=text, Incidents=Int64.Type, Running Total=Int64.Type] 
    )
in
    Combine

Old suboptimal FirstN code for reference:

let
    Source = Sample,
    Incidents = List.Buffer( Source[Incidents] ),
    Run = List.Transform( 
        List.Positions(Incidents), 
        each List.Sum( List.FirstN( Incidents, _+1 ) ) 
    ),
    Combine = Table.FromColumns( 
        Table.ToColumns(Source) & { Run }, 
        type table [Month=text, Incidents=Int64.Type, Running Total=Int64.Type] 
    )
in
    Combine

[raisurrahman]

You’re getting this error because you used only the column name [Incidents] in the function. To convert a column into a list, you must use the full TableName[ColumnName] format.

Please check the solution shared by @PhilipTreacy — it explains the correct approach.

[PhilipTreacy · Accepted solution]

Hi @scsos 

You need to refer to the column of Incidents using the name of the last step.  Assuming that last step was adding the Index column, your code would look like this

= List.Sum(List.FirstN(#"Added Index"[Incidents] , [Index]))

And you need to change the Index column so that it starts at 1, not 0.  If you start it at 0 the running total will be incorrect

Regards

Phil