Power Query Conditional Running Total with If Statement - Advanced Problem

[Spreadsheet Pete]

Hi,

This is an Advanced problem in M language with a very simple excel formula solution.
And after many tryouts with many codes, I am kindly asking for Help, please.



I have managed to do the grouped running total in various methods yet could not twist any of the codes to achieve the below excel example.



The question is how to do a group running total that evaluates each step that if the next added line will be greater than the calculation of the passed variable and the sum up to this point and if it is greater than that calculation it adds the calculation to the sum and continues running total with the passed calculation.



Data is as simple as 3 columns: Filter - to group on, Volume - to sum up, and Cube - as a variable to perform the calculation.



Here are the basic example of excel files explaining the problem statement

in Line F10 you can see that formula triggers the if statement, that if the running total in F10(adding B10; B10 Greater than J9) is greater than Remaining space in J9 then it adds the J9 to the running total.

Where J9 is the calculation based on F9 and C9 etc...

Desired outcome

Formulas

The best M code so far was by following the link below, however I have failed measurable by trying to enhance this code to my calculations

[POWER QUERY] Grouped Running Totals with a maximum condition and a verifier

Good evening, people. I'm in need of a function on power query that sums the running total across its categories, but compares the running total value to a previously defined maximum for its category. If running total is greater than its maximum, it subtracts its own value and remains the same...

www.mrexcel.com

I have also tried this

`(values as list, grouping as list, cube as list) as list =>
let GRTList = List.Generate
( ()=> [ GRT = values{0}, i =0, cube =0],
each [i] < List.Count(values),
each try if grouping{[i]} = grouping{[i]+1}
and [GRT = [GRT] + values{[i]+1}, i=[i]+1]> Number.RoundUp([GRT]/
cube)*cube
then [GRT = [GRT] + values{[i]+1}, i=[i]+1]+((
Number.RoundUp([GRT]/ cube)*cube) -[GRT])
else [GRT = [GRT] + values{[i]+1}, i=[i]+1]

otherwise [i= [i]+1],
each[GRT]
)
in
GRTList

And the call out function

#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0,
 1, Int64.Type),

 BufferedTable5 = Table.Buffer(#"Added Index"),

 BufferedValues = List.Buffer(BufferedTable5[Volume]),
 BufferedFilter = List.Buffer(BufferedTable5[Filter for Running
  total_Drop_Level]),
 BufferedCube = List.Buffer(BufferedTable5[Cube Per Posission]),
 RT = Table.FromColumns({BufferedTable5[Filter for Running
 total_Drop_Level],BufferedTable5[Volume],
 
fxGroupedRunningTotal(BufferedValues,BufferedFilter,BufferedCube)},

Ho to twist these codes to achieve the calculation, please?

Many thanks

Piotr

 

[Spreadsheet Pete]

Here is the link to the solution post

Post with Solussion