Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

https://www.mrexcel.com/board/threads/power-query-grouped-running-totals-with-a-maximum-condition-an...

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

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

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

Impactful Individual

I corrected the code in fxCalc.

``````(A)=>
let
RunningTotal = Table.AddColumn(
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})))
),
CubeFill = Table.AddColumn(
RunningTotal,
"Cube Fill",
each [Running Total]/[Cube]
),
PositionCount = Table.AddColumn(
CubeFill,
"Position Count",
each Number.RoundUp([Cube Fill],0)
),
RemainingSpace = Table.AddColumn(
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
Impactful Individual

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],
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1,
Int64.Type),
CorrectTypes = Table.TransformColumnTypes(#"Added Index",{{"Index",
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
A = Table.AddIndexColumn(_,"i")
in Table.AddColumn(A,"R", each
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

Impactful Individual

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
RunningTotal = Table.AddColumn(
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})))
),
CubeFill = Table.AddColumn(
RunningTotal,
"Cube Fill",
each [Running Total]/[Cube]
),
PositionCount = Table.AddColumn(
CubeFill,
"Position Count",
each Number.RoundUp([Cube Fill],0)
),
RemainingSpace = Table.AddColumn(
PositionCount,
"Remaining Space",
each [Position Count] * [Cube] - [Running Total]
)
in
RemainingSpace

Regards

KT

Impactful Individual

I corrected the code in fxCalc.

``````(A)=>
let
RunningTotal = Table.AddColumn(
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})))
),
CubeFill = Table.AddColumn(
RunningTotal,
"Cube Fill",
each [Running Total]/[Cube]
),
PositionCount = Table.AddColumn(
CubeFill,
"Position Count",
each Number.RoundUp([Cube Fill],0)
),
RemainingSpace = Table.AddColumn(
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

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors