Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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 |
Solved! Go to 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.
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"
Works very well an very compact indeed. Impressive.
Thanks and all the best!
Also this one works very well and useful not the least for my learning as well, thanks a lot!
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!
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.
Works very well an very compact indeed. Impressive.
Thanks and all the best!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
24 | |
13 | |
12 | |
11 | |
8 |
User | Count |
---|---|
43 | |
26 | |
16 | |
15 | |
12 |