Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
12 | |
8 | |
8 | |
7 |
User | Count |
---|---|
15 | |
13 | |
9 | |
7 | |
6 |