Conditional running total on categories

[PQRookie]

Hi,

I am trying to create a conditional running total on categories, shown in the 'result' column, but not veryr successful.
For each category it should sum the values in case condition is 'Yes'.

I have a rather common 'fxRunningTotal' function that I have tried to modify with an if statement in the List.Generate-function, but clearly not doing it right.

Would appreciate help!

Table:

date	category	condition	value	result
2024-04-03	A	Yes	10	10
2024-04-04	A	No	15	10
2024-04-05	B	Yes	10	10
2024-04-06	A	Yes	20	30
2024-04-07	B	No	20	10

[v-xinruzhu-msft · Accepted solution]

Hi @PQRookie 

You can create a blank query and put the following code to advanced editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtE1ACJjJR0lRyCOTC0GkoYGSrE6SLImUFm/fJCkKaqkKVDMCadWMxSDjdBkzaF6wQaDJGMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, category = _t, condition = _t, value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"category", type text}, {"condition", type text}, {"value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Sum(Table.SelectRows(#"Changed Type",(x)=>x[category]=[category] and x[condition]="Yes" and x[date]<=[date])[value]))
in
    #"Added Custom"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ronrsnfld · Accepted solution]

Here's another method that relies on Grouping rather than 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtE1ACJjJR0lRyCOTC0GkoYGSrE6SLImUFm/fJCkKaqkKVDMCadWMxSDjdBkzaF6wQaDJGMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, category = _t, condition = _t, value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"date", type date}, {"category", type text}, {"condition", type text}, {"value", Int64.Type}}),

//add index column to preserve original order
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),

//Add conditional running total column for each group
    #"Grouped Rows" = Table.Group(#"Added Index", {"category"}, {

        {"Result", (t)=>
            let 
                runTotal = List.Generate(
                    ()=>[rt=if t[condition]{0}="Yes" then t[value]{0} else 0, idx=0],
                    each [idx] < Table.RowCount(t),
                    each [rt=if t[condition]{[idx]+1} = "Yes" then [rt]+t[value]{[idx]+1} else [rt], idx=[idx]+1],
                    each [rt]),
                addCol = Table.FromColumns(
                        Table.ToColumns(t) &
                        {runTotal}, {"date","category","condition","value","Index","result"})
            in 
                addCol, 
            type table [date=nullable date, category=nullable text, condition=nullable text, 
                        value=nullable number, Index=Int64.Type, result=nullable number]}}),
    #"Expanded Result" = Table.ExpandTableColumn(#"Grouped Rows", "Result", {"date", "condition", "value", "Index", "result"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Result",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns"

[PQRookie · Accepted solution]

Works very well an very compact indeed. Impressive.

Thanks and all the best!

[ronrsnfld · Accepted solution]

Here's another method that relies on Grouping rather than 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtE1ACJjJR0lRyCOTC0GkoYGSrE6SLImUFm/fJCkKaqkKVDMCadWMxSDjdBkzaF6wQaDJGMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, category = _t, condition = _t, value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"date", type date}, {"category", type text}, {"condition", type text}, {"value", Int64.Type}}),

//add index column to preserve original order
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),

//Add conditional running total column for each group
    #"Grouped Rows" = Table.Group(#"Added Index", {"category"}, {

        {"Result", (t)=>
            let 
                runTotal = List.Generate(
                    ()=>[rt=if t[condition]{0}="Yes" then t[value]{0} else 0, idx=0],
                    each [idx] < Table.RowCount(t),
                    each [rt=if t[condition]{[idx]+1} = "Yes" then [rt]+t[value]{[idx]+1} else [rt], idx=[idx]+1],
                    each [rt]),
                addCol = Table.FromColumns(
                        Table.ToColumns(t) &
                        {runTotal}, {"date","category","condition","value","Index","result"})
            in 
                addCol, 
            type table [date=nullable date, category=nullable text, condition=nullable text, 
                        value=nullable number, Index=Int64.Type, result=nullable number]}}),
    #"Expanded Result" = Table.ExpandTableColumn(#"Grouped Rows", "Result", {"date", "condition", "value", "Index", "result"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Result",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns"

[PQRookie · Accepted solution]

Also this one works very well and useful not the least for my learning as well, thanks a lot!

[v-xinruzhu-msft · Accepted solution]

Hi @PQRookie 

You can create a blank query and put the following code to advanced editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtE1ACJjJR0lRyCOTC0GkoYGSrE6SLImUFm/fJCkKaqkKVDMCadWMxSDjdBkzaF6wQaDJGMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, category = _t, condition = _t, value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"category", type text}, {"condition", type text}, {"value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Sum(Table.SelectRows(#"Changed Type",(x)=>x[category]=[category] and x[condition]="Yes" and x[date]<=[date])[value]))
in
    #"Added Custom"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PQRookie · Accepted solution]

Works very well an very compact indeed. Impressive.

Thanks and all the best!