Forum Discussion
calculate a running total based on a filter
Hi,
I need to calculate a running total based on a filter, Customer in the table below. Balance column is calculated subtracting Added column from Closed column for Each Customer. I need to have a running balance by each customer. Can I do this in calculated column instead of a measure. Thanks for the help.
VJ_Lsf Hi! Here the Advanced Editor Codefor your scope:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnR0VNJRMgFiQwOlWB0UAUMkASMjkIgRkogZSMAYScACJGACFnBycgJyjEECpkgCYFPNkASAdgIJcwwRC6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Balance = _t, Week = _t]),
// Convert column types
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Balance", Int64.Type}, {"Week", Int64.Type}}),// Sort by Customer, then Week ascending
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Customer", Order.Ascending}, {"Week", Order.Ascending}}),// Add Index by Customer
#"Grouped" = Table.Group(#"Sorted Rows", {"Customer"}, {
{"AllData", each
Table.AddIndexColumn(_, "Index", 0, 1, Int64.Type)
}
}),
// Expand the grouped table
#"Expanded Table" = Table.ExpandTableColumn(#"Grouped", "AllData", { "Balance", "Week", "Index"}),// Add Running Total using List.Sum over previous rows
#"Added Running Total" = Table.AddColumn(#"Expanded Table", "RunningBalance", (row) =>
let
customer = row[Customer],
index = row[Index],
balances = Table.SelectRows(#"Expanded Table", each [Customer] = customer and [Index] <= index)[Balance]
in
List.Sum(balances)
),// Remove the index if not needed
#"Removed Columns" = Table.RemoveColumns(#"Added Running Total",{"Index"})in
#"Removed Columns"You'll obtain the Running Balance for each Customer by Week ASC:
If it's ok, please accept my answer as solution!
BBF
2 Replies
VJ_Lsf Hi! Here the Advanced Editor Codefor your scope:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnR0VNJRMgFiQwOlWB0UAUMkASMjkIgRkogZSMAYScACJGACFnBycgJyjEECpkgCYFPNkASAdgIJcwwRC6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Balance = _t, Week = _t]),
// Convert column types
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Balance", Int64.Type}, {"Week", Int64.Type}}),// Sort by Customer, then Week ascending
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Customer", Order.Ascending}, {"Week", Order.Ascending}}),// Add Index by Customer
#"Grouped" = Table.Group(#"Sorted Rows", {"Customer"}, {
{"AllData", each
Table.AddIndexColumn(_, "Index", 0, 1, Int64.Type)
}
}),
// Expand the grouped table
#"Expanded Table" = Table.ExpandTableColumn(#"Grouped", "AllData", { "Balance", "Week", "Index"}),// Add Running Total using List.Sum over previous rows
#"Added Running Total" = Table.AddColumn(#"Expanded Table", "RunningBalance", (row) =>
let
customer = row[Customer],
index = row[Index],
balances = Table.SelectRows(#"Expanded Table", each [Customer] = customer and [Index] <= index)[Balance]
in
List.Sum(balances)
),// Remove the index if not needed
#"Removed Columns" = Table.RemoveColumns(#"Added Running Total",{"Index"})in
#"Removed Columns"You'll obtain the Running Balance for each Customer by Week ASC:
If it's ok, please accept my answer as solution!
BBF
Hi VJ_Lsf , there's another solution you could look. I haven't made the grouping here since there were only one set of customers. If there are more, you can do the grouping by customer first and then apply the below code for each and every grouped table. Thanks!
Here's the code:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Sum = List.Skip(List.Accumulate(Source[Balance], {0}, (s,c) => s & {List.Last(s) + c})),
Add = Source,
RemCols = Table.AddIndexColumn(Table.RemoveColumns(Add,{"Balance"}),"Balance",0,1),
Table = Table.TransformColumns(RemCols,{"Balance", each Sum{_}})
in
Table
