Forum Discussion
Row by row sumifs excel replicate
Dear BI gurus, I've refered to almost all of old threads in the forum but to no avail. I've been stucking for weeks now. I am pretty sure it can be done but it is so challenging to replicate ...
I started to look at options in M
These are my initial working, however it may still have some circular reference issues.
However I've learnt how to reference different rows.
http://excel-inside.pro/blog/2015/11/05/absolute-and-relative-references-in-power-query/
let Source = Excel.Workbook(File.Contents("L:\Downloads\P Bi example.xlsx"), null, true), Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Exp_site", type text}, {"Helper1", type text}, {"Exp_highstock", Int64.Type}, {"SKU", type text}, {"Imp_site", type text}, {"Helper2", type text}, {"Imp_Demand", Int64.Type}, {"Min Exp & Imp", Int64.Type}, {"Final Allocation", Int64.Type}, {"Exp_remainder", Int64.Type}, {"Imp_remainder", type any}, {"Explaination", type text}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Min Exp & Imp", "Final Allocation", "Exp_remainder", "Imp_remainder", "Explaination"}), #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1), #"Changed Type1" = Table.TransformColumnTypes(#"Added Index",{{"Index", Int64.Type}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Exp_site] <> null)), #"Added Custom" = Table.AddColumn(#"Filtered Rows" , "MinExp&Imp", each List.Min({[Exp_highstock],[Imp_Demand]})), #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "FinalAllocation", each if [Index] = 0 then [#"MinExp&Imp"] else 0), #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "ExpRemainder", each if [Index] = 0 then [Exp_highstock]-[FinalAllocation] else 0), #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "ImpRemainder", each if [Index] = 0 then [Imp_Demand] else 0), #"Invoked Custom Function" = Table.AddColumn(#"Added Conditional Column2", "FuncRes", each fnSumIF([SKU], [Index], #"Added Conditional Column2")), #"Added Custom2" = Table.AddColumn(#"Invoked Custom Function", "RefRelative", each Source{[Index]-1}[Index]) in #"Added Custom2"and a function calld fnSumIF
let Source = (FilterSku as text, FilterRow as number, FilterSource as table) => let FilteredData = Table.SelectRows(FilterSource , each ([Index]<FilterRow )), #"Grouped Rows" = Table.Group(FilteredData , {"SKU"}, {{"Sum", each List.Sum([Exp_highstock]), type number}}), Result = #"Grouped Rows"{[SKU=FilterSku]}[Sum] in Result in Source
M is not as powerful at doing selective aggregations, however it has still has lots of power and you can create functions that can run over each row.
If the data sets are not too large then yes M could be a good option.
If you get issues with circular references it may be possible to duplicate the query to remove the problem.
I've not done anything as tricky as your problem with M so I'd be interested to know how you get on.
Maybe the solution will be part M part DAX.
I started to look at options in M
These are my initial working, however it may still have some circular reference issues.
However I've learnt how to reference different rows.
http://excel-inside.pro/blog/2015/11/05/absolute-and-relative-references-in-power-query/
let
Source = Excel.Workbook(File.Contents("L:\Downloads\P Bi example.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Exp_site", type text}, {"Helper1", type text}, {"Exp_highstock", Int64.Type}, {"SKU", type text}, {"Imp_site", type text}, {"Helper2", type text}, {"Imp_Demand", Int64.Type}, {"Min Exp & Imp", Int64.Type}, {"Final Allocation", Int64.Type}, {"Exp_remainder", Int64.Type}, {"Imp_remainder", type any}, {"Explaination", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Min Exp & Imp", "Final Allocation", "Exp_remainder", "Imp_remainder", "Explaination"}),
#"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Index",{{"Index", Int64.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Exp_site] <> null)),
#"Added Custom" = Table.AddColumn(#"Filtered Rows" , "MinExp&Imp", each List.Min({[Exp_highstock],[Imp_Demand]})),
#"Added Conditional Column" = Table.AddColumn(#"Added Custom", "FinalAllocation", each if [Index] = 0 then [#"MinExp&Imp"] else 0),
#"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "ExpRemainder",
each if [Index] = 0 then [Exp_highstock]-[FinalAllocation] else 0),
#"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "ImpRemainder", each if [Index] = 0 then [Imp_Demand] else 0),
#"Invoked Custom Function" = Table.AddColumn(#"Added Conditional Column2", "FuncRes", each fnSumIF([SKU], [Index], #"Added Conditional Column2")),
#"Added Custom2" = Table.AddColumn(#"Invoked Custom Function", "RefRelative", each Source{[Index]-1}[Index])
in
#"Added Custom2"and a function calld fnSumIF
let
Source = (FilterSku as text, FilterRow as number, FilterSource as table) => let
FilteredData = Table.SelectRows(FilterSource , each ([Index]<FilterRow )),
#"Grouped Rows" = Table.Group(FilteredData , {"SKU"}, {{"Sum", each List.Sum([Exp_highstock]), type number}}),
Result = #"Grouped Rows"{[SKU=FilterSku]}[Sum]
in
Result
in
Source