Forum Discussion
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 | 12508.91667 |
| 59 | -3784.75 | 201927 | 9665.81667 |
| 59 | -1225.71 | 201928 | 5881.06667 |
| 59 | 4297.863 | 201929 | 4655.35667 |
| 59 | 2311.908 | 201930 | 8953.219803 |
| 59 | -1143.34 | 201931 | 11265.12787 |
| 59 | 2283.867 | 201932 | 10121.78334 |
| 59 | -425.983 | 201933 | 12405.65 |
| 59 | -1411.55 | 201934 | 11979.66667 |
I have put this together in Excel but now need to replicate it in Power Query. The Opening Stock Column is what I am trying to achieve. In Excel the formula in Cell D3 down is =SUMIFS(B:B,C:C,"<"&C3,A:A,A3)
How is this written in Power Query
Any help would be appreciated.
Thanks
Simon
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"
7 Replies
You need to have date/calendar dimension.
sales_till_tody =CALCULATE(sum(Sales[Sales Amount]),filter(sales,Sales[Sales Date]<=maxx(Sales,Sales[Sales Date].[Date])))
Am I able to use that in Power Query?
I have found List.Accumulate but i can't get it to work?
The code below presumes that your data is in a table named "Table2". The process is to add an index column to the table that starts at 0. Then take the First chacters up to the index amount from the prior table B column and sum them up.
This technique may be slow if you have a large table. In that case, we would add a List.Buffer as a separate step on the #"Added Index" table.
let Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", type number}, {"C", Int64.Type}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1), #"Added Custom" = Table.AddColumn(#"Added Index", "Opening Stock.1", each List.Sum(List.FirstN(#"Added Index"[B],[Index]))) in #"Added Custom"Regards,
Mike
