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've not fully digested your problem, but here are a couple of things that may help.
First be careful just reproducting a excel solution as powerbi models are very different and it may be better to redesign the structure of your data into tables.
Not sure how much you know about powerbi models. This video is a good starting point if your new to it.
https://www.youtube.com/watch?v=pvIVMEFQokE
M is not so strong on aggreations, but it does have group and sum functions. The IF part could be done with new conditional columns before grouping.
However I would probably try and do it in DAX using the SWITCH function for the IF part and the you can aggregate the results.
Some examples.
https://powerpivotpro.com/2012/06/dax-making-the-case-for-switch/
I like to use SWITCH(TRUE(),
followed by a series of true/false conditions.
I've also used the New Table option under the modeling tab to create a summarised table based on Sums of other calculated columns such as Swtch expressions. To do this I used the AddColumns Dax Function https://msdn.microsoft.com/en-us/library/gg492204.aspx
Hope this gets you going in the right direction.
Thanks stretcharm for nice references.
I've considered DAX but i found it hard to reproduce the formula that dynamically.
To understand my problem, let's dig into the following instance
Suppose you own 6 convenience stores: X Y Z C D E
Summer comes, sales of beer and peanut rise unexpectedly, which drives C & D & E running short of stock.
Their inventory need replenishing asap to avoid loss sales.
While around the area X Y Z have a lot of avaiable high stock ready for shipping
The problem is to allocate beer and peanut from X Y Z to C D E so that the transport cost is minimum, and the site with highstock can transfer goods as much as possible to the one in need of stock.
To do that, I shall need to have a table like below:
Exp_site Helper1 Exp_highstock SKU Imp_site Helper2 Imp_Demand Min Exp & Imp Final Allocation Exp_remainder Imp_remainder Explaination X X_Beer 80 Beer D D_Beer 110 80 80 0 30 X has allocated 80 pcs of Beer to D whose total demand is 110, which means that there are still 30 pcs left for next allocation round Y Y_Peanut 20 Peanut C C_Peanut 30 20 20 20 30 Y has allocated 20 pcs of Peanut to C whose total demand is 30, which means that C has 20 left for next allocation round, Y ran out of available stock Z Z_Beer 40 Beer D D_Beer 110 40 30 40 30 Z has allocated the remaining 30 pcs of site D, which means there are none left for next allocation round Y Y_Peanut 20 Peanut E E_Peanut 15 15 0 0 15 Due to Y having run out of available stock, Y can't allocate more to E Conclusion: D takes 80 beer pcs from X, 30 pcs from Z
C take 20 peanut pcs from Y
E has no peanuts since we can't find any store with available stock to transferI imagine I can use index and List.Sum to replicate sumifs but still stuck in vain.
The orginal excel file can be found here:
https://drive.google.com/file/d/1nTA2iV5lmeRwNtlIQu42XP49jF1KGvYc/view?usp=sharing
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?