SUM IF power query???

mechanix85

In this scenario, to calculate cumulative total group on column, you need to group rows into a table. Then create a custom function and pass table as argument to get the running total.

Firstly, please substring the Stage column to get an index column within each Order, and change the data type into "Whole Number".

Then group the table on Order column.

And create a custom function like:

RunningTotal = (tabletocum as table) => 
        let 
            AddRunningTotal = Table.AddColumn(tabletocum, "Running Total", each List.Accumulate(List.Range(tabletocum[Value],0,[Index]),0,(state, current) => state + current))    
        in
        AddRunningTotal,

See my full M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8i9KSS0yVNJRCi5JTE8FMQyVYnXQxY2ADCMs4sZAhikWcRMgwxiLuCmq+UbI9hphETdCNccIzd5YAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Order = _t, Stage = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order", type text}, {"Stage", type text}, {"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Index", each Text.AfterDelimiter([Stage],"Stage")),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Index", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Order"}, {{"AllRows", each _, type table}}),
    RunningTotal = (tabletocum as table) => 
        let 
            AddRunningTotal = Table.AddColumn(tabletocum, "Running Total", each List.Accumulate(List.Range(tabletocum[Value],0,[Index]),0,(state, current) => state + current))    
        in
        AddRunningTotal,
        Addedcum = Table.TransformColumns(#"Grouped Rows", {"AllRows", each RunningTotal(_)}),
    #"Expanded AllRows" = Table.ExpandTableColumn(Addedcum, "AllRows", {"Stage", "Value", "Index", "Running Total"}, {"AllRows.Stage", "AllRows.Value", "AllRows.Index", "AllRows.Running Total"})
in
    #"Expanded AllRows"

Regards,