Acumulative Totals suming previous row

[ccast]

Hi everybody!

I need your help with the following

I am allocating stock of different products according to the availability of each store for each product.

The problem is that when I do the grouping considering Product and Total to allocate, Power Query uses the availability for the previous product in the same store instead of using the one for that product. 

[dufoq3]

Hi, what is expected output based on Plain Data?

[ccast]

Hi @dufoq3 

See below: the total to allocate should be applied from store A to B and to C applying the remaining total following a particular order (A, B and C). 

Plain Data:

Product	Store	Store Needs	Total to Allocate	Allocation	Excess/Missing
JIDH1	A	200	350	200	0
JIDH1	B	100	350	100	0
JIDH1	C	100	350	50	-50
JIDH3	A	0	5000	0	0
JIDH3	B	2000	5000	2000	0
JIDH3	C	30	5000	30	2700

At the moment, I am getting the data incorrectly as Power Query uses the needs for the first product on the second one:

Error: 

Product	Store	Store Needs	Total to Allocate	Allocation	Excess/Missing
JIDH1	A	200	350	200	0
JIDH1	B	100	350	100	0
JIDH1	C	100	350	50	-50
JIDH3	A	0	5000	200	-200
JIDH3	B	2000	5000	100	-100
JIDH3	C	30	5000	100	-100

[AlienSx · Accepted solution]

source

code

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    fx = (tbl) => 
        [lst = List.Buffer(Table.ToRows(tbl)),
        excess = tbl[Total to Allocate]{0} - List.Sum(tbl[Store Needs]),
        gen = List.Generate(
            () => 
                [
                    i = 0, 
                    alloc = List.Min({lst{0}{2}, lst{0}{3}}), 
                    stock = lst{0}{3} - alloc
                ],
            (x) => x[i] < List.Count(lst),
            (x) => 
                [
                    i = x[i] + 1, 
                    alloc = List.Min({lst{i}{2}, x[stock]}),
                    stock = x[stock] - alloc
                ],
            (x) => lst{x[i]} & {x[alloc], if x[i] = List.Count(lst) - 1 then excess else 0}
        )][gen],
    group = Table.Group(
        Source, 
        "Product", 
        {"x", fx}
    ),
    tbl = Table.FromRows(List.Combine(group[x]), Table.ColumnNames(Source) & {"Allocated", "Excess/Missing"})
in
    tbl

 result

[ccast]

It worked! thank you so much!

[dufoq3]

Hi @ccast, provide sample data in usable format and expected result based on that.