Forum Discussion
Solved
Acumulative Totals suming previous row
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 ...
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 tblresult
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
Super User
Super Usersource
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
tblresult
It worked! thank you so much!