Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Power Query
- Re: Power Query Conditional Running Total with If ...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Power Query Conditional Running Total with If Statement - Advanced Problem

07-20-2022
02:44 AM

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...

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

Solved! Go to Solution.

2 ACCEPTED SOLUTIONS

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-20-2022
07:29 PM

Hi @SpreadsheetPete,

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-21-2022
06:29 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-21-2022
02:14 AM

HI @SpreadsheetPete ,

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-21-2022
04:12 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-21-2022
06:08 AM

Hi @SpreadsheetPete ,

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-21-2022
06:29 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-21-2022
06:49 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-20-2022
07:29 PM

Hi @SpreadsheetPete,

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-21-2022
04:21 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-21-2022
05:25 AM

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...

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-21-2022
05:02 AM

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

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors

User | Count |
---|---|

36 | |

31 | |

21 | |

14 | |

13 |

Top Kudoed Authors

User | Count |
---|---|

41 | |

39 | |

33 | |

22 | |

20 |