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
Hi,
I had a look at your example and have something to get you a bit further, however stuck with circular references.
I added a index in the Query Editor and then new Computed Columns
FinalAllocation =
VAR FinAll =
IF ( Sheet1[Index] = 0, MIN ( Sheet1[Exp_highstock], Sheet1[Imp_Demand] ), 0 )
RETURN
IF ( FinAll = 0, MIN ( Sheet1[ExpRemainder], Sheet1[ImpRemainder] ), FinAll )
You can do sumifs like this for pervious rows, Note EARLIER means an earlier evaluation not row.
SumImpProduct =
SUMX (
FILTER (
Sheet1,
Sheet1[SKU] = EARLIER(Sheet1[SKU])
&& Sheet1[Index] <= EARLIER ( Sheet1[Index] )
),
Sheet1[Imp_Demand]
)
Unfortunlatley these expressions doent work due to circular references.
ImpRemainder =
VAR rowSKU = Sheet1[SKU]
RETURN
Sheet1[Imp_Demand] -
SUMX (
FILTER (
Sheet1,
Sheet1[SKU] = rowSKU
&& Sheet1[Index] < EARLIER ( Sheet1[Index] )
),
Sheet1[FinalAllocation]
)ExpRemainder =
VAR rowSKU = Sheet1[SKU]
RETURN
Sheet1[Exp_highstock] -
SUMX (
FILTER (
Sheet1,
Sheet1[SKU] = rowSKU
&& Sheet1[Index] < EARLIER ( Sheet1[Index] )
),
Sheet1[FinalAllocation]
)Each of these agregation expressions runs over the whole record set, but you can filter the data.
I've not got a solution to your particular example.
Maybe these expressions can help you to find a solution.
Here are some videos that help explain the dax calculations I've used.
Sum Sub Categories
https://www.youtube.com/watch?v=mxDt81H8hDg&index=58&list=PLDz00l_jz6zym_YP8ZW11o52niGfCP8pN
Calculate function videos inc dax similar to sumif and the use of earlier
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