cancel
Showing results for
Did you mean: Regular Visitor

## Power Query Conditional Running Total with If Statement - Advanced Problem

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... This is the Example data with desire outcome Here are the formulas

The best M code so far was by following the link below

Here is my code

```````(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 here is the callout function

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

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)},``````

Many thanks,

2 ACCEPTED SOLUTIONS  Memorable Member

This is probably not the neatiest solution, but you can try something like this:

``````let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLQMzACU4amQMpQKVYHLm6KEDdCFjdHiBvjUG+CLG6JEDcFizth2GuGLI5kjjmyOJK9FmBxZwxxSyRxEyRzDA2QNSA5yBDo41gA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Filter = _t, Volume = _t, Cube = _t, Index = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Filter", type text}, {"Volume", type number}, {"Cube", type number}, {"Index", type text}}),
Aggregate = List.Accumulate(Table.ToRecords(#"Changed Type"), {}, (a,n)=> a & {
Record.AddField(Record.AddField(n, "CubeFill", n[Volume] + (if List.IsEmpty(a) or List.Last(a)[CubeFill] + n[Volume] > n[Cube] or List.Last(a)[Filter] <> n[Filter] then 0 else List.Last(a)[CubeFill])),
"PositionCount", if List.IsEmpty(a) or List.Last(a)[Filter] <> n[Filter] then 1 else if List.Last(a)[CubeFill] + n[Volume] > n[Cube] then List.Last(a)[PositionCount]+Number.RoundUp(n[Volume]/n[Cube], 0)  else List.Last(a)[PositionCount])}),
Output = Table.FromRecords(Aggregate)
in
Output``````

Kind regards,

John  Super User

I corrected the code in fxCalc.

``````(A)=>
let
A,
"Running Total",
each
if [Volume] <
(Number.RoundUp(List.Sum(List.InsertRange(List.FirstN(A[Volume],[Index]),0,{0})) / [Cube], 0) * [Cube])
- List.Sum(List.InsertRange(List.FirstN(A[Volume],[Index]),0,{0}))
then @RunningTotal[Running Total]{[Index]-1} + [Volume]
else List.Sum(List.FirstN(A[Volume],[Index]+1))
+ ((Number.RoundUp(List.Sum(List.InsertRange(List.FirstN(A[Volume],[Index]),0,{0})) / [Cube], 0) * [Cube])
- List.Sum(List.InsertRange(List.FirstN(A[Volume],[Index]),0,{0})))
),
RunningTotal,
"Cube Fill",
each [Running Total]/[Cube]
),
CubeFill,
"Position Count",
each Number.RoundUp([Cube Fill],0)
),
PositionCount,
"Remaining Space",
each [Position Count] * [Cube] - [Running Total]
)
in
RemainingSpace`````` Main Query:

``````let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLQMzACU4amSrE6cDFTLGLmRKqzRBZzwmKHExa9TljscMYpZmKKRR3C3lgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Filter = _t, Volume = _t, Cube = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Filter", type text}, {"Volume", type number}, {"Cube", type number}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Filter"}, {{"Group", each fxCalc(Table.AddIndexColumn(_,"Index",0,1))}}),
Combine = Table.Combine(#"Grouped Rows"[Group])
in
Combine``````

Regards

KT

9 REPLIES 9  Super User

The challenge I can foresee now is the cyclic reference based on your excel formula between the Remaining Space and Running Total column. I will have an attempt with the recursive method later and let you know if any luck.

Regards

KT Regular Visitor

Amazing Thank you

I did have some luck by adopting the below code and it works very closely yet it causes missed calculations after only some of the records. here is my latest code.

Also here is the updated Excel file demonstrating the output of the code and the error

the best code so far

``````let
Source = Excel.CurrentWorkbook(){[Name="SourceTable"]}[Content],
Int64.Type),
Int64.Type}, {"Filter", type text}, {"Volume", type number}}),

each List.Sum(Table.SelectRows(CorrectTypes, (Q) =>  Q[Filter] =
[Filter] and Q[Index] <= [Index])[Volume]), type number),

Runing = Table.Group(CorrectTypes,"Filter",{"A", each let
List.Accumulate(Table.SelectRows(A, (a)=> a[i]<=[i])[Volume],
[Running=0, Verifier = 1],
// Here the challenge begins
(s,l)=> [Running = if s[Running]+l >
(Number.RoundUp(s[Running]+[Volume]/[Cube])*[Cube]) then
((Number.RoundUp(s[Running]/[Cube])*[Cube])-s[Running])+s[Running]+
[Volume] else s[Running]+l  , Verifier =Number.From(s[Running]+l
<=Number.RoundUp(s[Running]/[Cube])*[Cube] )]  ))
}),
ExpandedR = Table.ExpandRecordColumn(
Table.ExpandTableColumn(Runing, "A", {"Volume","Cube","i", "R"}),
"R", {"Running", "Verifier"})
in``````

and here is the output Code Output and formulas with desired outcome  Super User

Please see below code (There are still a slight variance in the calculation which I will look into it tomorrow.)

Working query:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLQMzACU4amSrE6cDFTLGLmRKqzRBZzwmKHExa9TljscMYpZmKKRR3C3lgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Filter = _t, Volume = _t, Cube = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Filter", type text}, {"Volume", type number}, {"Cube", type number}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Filter"}, {{"Group", each fxCalc(Table.AddIndexColumn(_,"Index",0,1))}}),
Combine = Table.Combine(#"Grouped Rows"[Group])
in
Combine fxCalc:

(A)=>
let
A,
"Running Total",
each
if [Volume] <
(Number.RoundUp(List.Sum(List.InsertRange(List.FirstN(A[Volume],[Index]),0,{0})) / [Cube], 0) * [Cube])
- List.Sum(List.InsertRange(List.FirstN(A[Volume],[Index]),0,{0}))
then List.Sum(List.FirstN(A[Volume],[Index]+1))
else List.Sum(List.FirstN(A[Volume],[Index]+1))
+ ((Number.RoundUp(List.Sum(List.InsertRange(List.FirstN(A[Volume],[Index]),0,{0})) / [Cube], 0) * [Cube])
- List.Sum(List.InsertRange(List.FirstN(A[Volume],[Index]),0,{0})))
),
RunningTotal,
"Cube Fill",
each [Running Total]/[Cube]
),
CubeFill,
"Position Count",
each Number.RoundUp([Cube Fill],0)
),
PositionCount,
"Remaining Space",
each [Position Count] * [Cube] - [Running Total]
)
in
RemainingSpace

Regards

KT  Super User

I corrected the code in fxCalc.

``````(A)=>
let
A,
"Running Total",
each
if [Volume] <
(Number.RoundUp(List.Sum(List.InsertRange(List.FirstN(A[Volume],[Index]),0,{0})) / [Cube], 0) * [Cube])
- List.Sum(List.InsertRange(List.FirstN(A[Volume],[Index]),0,{0}))
then @RunningTotal[Running Total]{[Index]-1} + [Volume]
else List.Sum(List.FirstN(A[Volume],[Index]+1))
+ ((Number.RoundUp(List.Sum(List.InsertRange(List.FirstN(A[Volume],[Index]),0,{0})) / [Cube], 0) * [Cube])
- List.Sum(List.InsertRange(List.FirstN(A[Volume],[Index]),0,{0})))
),
RunningTotal,
"Cube Fill",
each [Running Total]/[Cube]
),
CubeFill,
"Position Count",
each Number.RoundUp([Cube Fill],0)
),
PositionCount,
"Remaining Space",
each [Position Count] * [Cube] - [Running Total]
)
in
RemainingSpace`````` Main Query:

``````let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLQMzACU4amSrE6cDFTLGLmRKqzRBZzwmKHExa9TljscMYpZmKKRR3C3lgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Filter = _t, Volume = _t, Cube = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Filter", type text}, {"Volume", type number}, {"Cube", type number}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Filter"}, {{"Group", each fxCalc(Table.AddIndexColumn(_,"Index",0,1))}}),
Combine = Table.Combine(#"Grouped Rows"[Group])
in
Combine``````

Regards

KT Regular Visitor

KT

THANK YOU VERY MUCH!!!

You Are The Winner Here as your code does exactly what I was struggling with for over a week now.

This is absolutely amazing! I cannot thank you enough

Beautiful work!

John,

Thank you to you too as your code is also very good and very useful and it has provided me a different perspective To my challenge so also very grateful for your work.

You are both lifesavers!

Kind Regards

Pete  Memorable Member

This is probably not the neatiest solution, but you can try something like this:

``````let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLQMzACU4amQMpQKVYHLm6KEDdCFjdHiBvjUG+CLG6JEDcFizth2GuGLI5kjjmyOJK9FmBxZwxxSyRxEyRzDA2QNSA5yBDo41gA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Filter = _t, Volume = _t, Cube = _t, Index = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Filter", type text}, {"Volume", type number}, {"Cube", type number}, {"Index", type text}}),
Aggregate = List.Accumulate(Table.ToRecords(#"Changed Type"), {}, (a,n)=> a & {
Record.AddField(Record.AddField(n, "CubeFill", n[Volume] + (if List.IsEmpty(a) or List.Last(a)[CubeFill] + n[Volume] > n[Cube] or List.Last(a)[Filter] <> n[Filter] then 0 else List.Last(a)[CubeFill])),
"PositionCount", if List.IsEmpty(a) or List.Last(a)[Filter] <> n[Filter] then 1 else if List.Last(a)[CubeFill] + n[Volume] > n[Cube] then List.Last(a)[PositionCount]+Number.RoundUp(n[Volume]/n[Cube], 0)  else List.Last(a)[PositionCount])}),
Output = Table.FromRecords(Aggregate)
in
Output``````

Kind regards,

John Regular Visitor

Hi John,

Thank you for the help, your code does the job only to a certain point

Where in 3rd step of calculation for Filter "A" adds to 0.14 the 0.05 it should add the remaining space from cube (0.15 - 0.14 = 0.1) to the current calculation 0.14+0.01 and then add the next line 0.05... the code reset and just start from 0.05 so it's missing the whole challenge I am facing 🙂 - I need Running total for entire Filter A but when remaining space is less than next cube to add it needs to absorb the remaining space to the running total. Regular Visitor

One hint of observation to note with this code, it heavily depends on the filter being sorted in group order before the index applies. if the filter is not sorted like AAABBBCCC and left as ABAABBACA it will just give possession 1 to every step...so wonder if we should apply the index to each grouped list.. rather than to the table... Regular Visitor

Hi John,

After dipper analysis of your code, it might not give me exactly what I was looking for but actually, it produces better results than I could hope for, it gives me the correct position against the cube. meaning I can simplify my further steps by adopting a different perspective to my problem. Amazing WORK!!! Thank you Very Much!!!

PS

If you do have an idea how to twist the code to earlier comments that would be great help /learning, please.  I am just so curious by now how I should have done it, hate to be defeated by a code 😉

It will cause me a sleep less nights till I resolve this challenge.

Kind regards

Pete Announcements #### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023. #### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers! #### 2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture. Top Solution Authors
Top Kudoed Authors
Users online (4,210)