Forum Discussion
Row by row sumifs excel 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
I am glad you understood my problems :( and thanks a lot for sparing time inventing some subtle DAX formulas.
As far as I am concerned, like you said since DAX formulas go through the whole records in lieu of row by row method, it will absolutely gets circular references.
That's why I opted to give up on DAX at the beginning and started to think of M_code as a last resort.
Do you seriously think we still stand a chance of solving it by DAX?
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