Forum Discussion
Solved
Sum If
How can I sum a column if the value is less than a certain value and matches another value. My Data is like this A B C Opening Stock 59 12508.92 201925 59 -2843.1 201926 1250...
Ooops copied the wrong query AND I did not put it in code block.
let Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content], #"Changed Type" = Table.Buffer(Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", type number}, {"C", Int64.Type}, {"Opening Stock", type number}})), #"Grouped Rows" = Table.Group(#"Changed Type", {"A"}, {{"AllRows", each _, type table}}), Transform = Table.TransformColumns(#"Grouped Rows",{{"AllRows", (tab) => Table.AddColumn(tab, "RunningTotal", each List.Sum(Table.SelectRows(tab, (row) => row[C] < [C])[B])) , type table}}), #"Expanded AllRows" = Table.ExpandTableColumn(Transform, "AllRows", {"B", "C", "RunningTotal"}, {"B", "C", "RunningTotal"}) in #"Expanded AllRows"
Ooops copied the wrong query AND I did not put it in code block.
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.Buffer(Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", type number}, {"C", Int64.Type}, {"Opening Stock", type number}})),
#"Grouped Rows" = Table.Group(#"Changed Type", {"A"}, {{"AllRows", each _, type table}}),
Transform = Table.TransformColumns(#"Grouped Rows",{{"AllRows",
(tab) => Table.AddColumn(tab, "RunningTotal", each List.Sum(Table.SelectRows(tab, (row) => row[C] < [C])[B]))
, type table}}),
#"Expanded AllRows" = Table.ExpandTableColumn(Transform, "AllRows", {"B", "C", "RunningTotal"}, {"B", "C", "RunningTotal"})
in
#"Expanded AllRows"
This looks perfect. Thanks so much!!