Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
PQRookie
Frequent Visitor

Conditional running total on categories

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:

datecategoryconditionvalueresult
2024-04-03AYes1010
2024-04-04ANo1510
2024-04-05BYes1010
2024-04-06AYes2030
2024-04-07BNo2010
4 ACCEPTED SOLUTIONS
v-xinruzhu-msft
Community Support
Community Support

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

vxinruzhumsft_0-1712626195714.png

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.

 

View solution in original post

ronrsnfld
Super User
Super User

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"

View solution in original post

Works very well an very compact indeed. Impressive.

Thanks and all the best!

View solution in original post

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

 

View solution in original post

4 REPLIES 4
ronrsnfld
Super User
Super User

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"

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

 

v-xinruzhu-msft
Community Support
Community Support

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

vxinruzhumsft_0-1712626195714.png

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.

 

Works very well an very compact indeed. Impressive.

Thanks and all the best!

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors