Row by row sumifs excel replicate

[Anonymous]

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 these formulas in M_code.

I have an allocation table to plan an optimal transfer quantity between sites so that the exporting site with maximum transfering capability can match an equivalent site with maximum receiving capability.

Each exporting site can have multiple options of receiving site and vice versa.

It can be demonstrated in Excel formula like below:

Article	Exp_site&sku	Max_transfer	Imp_site&sku	Max_receive	Unit Price	Final Allocation	Priority	Exp check	Imp check
10017038	254510017038	13	275510017038	10	114727	=MIN(AD2,R2)	1	=R2-SUMIF($J$2:J2,J2,$AF$2:AF2)	=AD2-SUMIF($AC$2:AC2,AC2,$AF$2:AF2)
10017038	254510017038	13	216810017038	10	114727	=MIN(AI98,AJ98)	2	=R98-SUMIF($J$2:J97,J98,$AF$2:AF97)	=AD98-SUMIF($AC$2:AC97,AC98,$AF$2:AF97)
10017038	254510017038	13	239110017038	10	114727	=MIN(AI222,AJ222)	3	=R222-SUMIF($J$2:J221,J222,$AF$2:AF221)	=AD222-SUMIF($AC$2:AC221,AC222,$AF$2:AF221)
10017038	254510017038	13	314210017038	10	114727	=MIN(AI302,AJ302)	4	=R302-SUMIF($J$2:J301,J302,$AF$2:AF301)	=AD302-SUMIF($AC$2:AC301,AC302,$AF$2:AF301)
10017038	254510017038	13	334610017038	5	114727	=MIN(AI441,AJ441)	6	=R441-SUMIF($J$2:J440,J441,$AF$2:AF440)	=AD441-SUMIF($AC$2:AC440,AC441,$AF$2:AF440)
10017038	254510017038	13	280410017038	5	114727	=MIN(AI511,AJ511)	7	=R511-SUMIF($J$2:J510,J511,$AF$2:AF510)	=AD511-SUMIF($AC$2:AC510,AC511,$AF$2:AF510)
10017038	254510017038	13	303810017038	10	114727	=MIN(AI699,AJ699)	10	=R699-SUMIF($J$2:J698,J699,$AF$2:AF698)	=AD699-SUMIF($AC$2:AC698,AC699,$AF$2:AF698)
10017038	254510017038	13	221710017038	5	114727	=MIN(AI824,AJ824)	12	=R824-SUMIF($J$2:J823,J824,$AF$2:AF823)	=AD824-SUMIF($AC$2:AC823,AC824,$AF$2:AF823)

Comes the result:

Article	Exp_site&sku	Max_transfer	Imp_site&sku	Max_receive	Unit Price	Final Allocation	Priority	Exp check	Imp check
10017038	254510017038	13	275510017038	10	114,727	10	1	3	0
10017038	254510017038	13	216810017038	10	114,727	3	2	3	10
10017038	254510017038	13	239110017038	10	114,727	0	3	0	10
10017038	254510017038	13	314210017038	10	114,727	0	4	0	10
10017038	254510017038	13	334610017038	5	114,727	0	6	0	5
10017038	254510017038	13	280410017038	5	114,727	0	7	0	5
10017038	254510017038	13	303810017038	10	114,727	0	10	0	10
10017038	254510017038	13	221710017038	5	114,727	0	12	0	5

So, the final allocation result will be:

- Site 2545, who can at best give away 13 pcs, will transfer 10 pcs and 3pcs of 10017038 to receving site 2755 and 2168 respectively.

Put simply, for each row, after allocating a specific quantity of pcs, the export site and importing site inventory will have a remainder, which is continously used for next row of allocation until its inventory is used up.

The catch is that, in the formula, for the first row of Final Allocation, I used a different formula, as the quantity equals: Min of (Max transfer and Min recevied)

While the next rows the final Quanity should be: Min of (Exp check and Imp check)

So please enlighten me with some suggestion of how I can replicate these tedious excel formula with M_code which can save me a great deal of time.

Thanks,

[stretcharm]

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.

[Anonymous]

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 transfer

I 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

[stretcharm]

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

https://curbal.com/blog/glossary/calculate-dax

[Anonymous]

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?

[stretcharm]

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.

[stretcharm · Accepted solution]

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