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

Reply
DiogoOishi
New Member

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 rt is greater than its maximum, it subtracts its own value and remains the same as (i-1). In addition, it should add 0 to a verification column.

 

I used this code from myonlinetraininghub (https://www.myonlinetraininghub.com/grouped-running-totals-in-power-query) and it solves part of my problem, but not the entirety of it

 

Here's a screenshot of an expected result:

DiogoOishi_0-1635873959136.png

I would be very pleased if anyone could help me with this. It doesn't seem to be difficult to solve, but I've tried several different solutions and none have worked, so I'm having a hard time with this. 

 

Thanks in advance

1 ACCEPTED SOLUTION
Vera_33
Resident Rockstar
Resident Rockstar

Hi @DiogoOishi 

 

Here is one way, merge the maxTable to get Max value for each Group, then add RT and Verifier

Vera_33_0-1635913748273.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc2xDQAhDAPAXVJTgBNE/f9jIPZfA1Aku/nu5NjKnPZYsbBVUq2TTmHoDrGpkN33ppWE6CqAivz13RXVQ6TCScfP6Ght", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Group = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Group", type text}, {"Value", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Group"}, maxTable, {"Group"}, "maxTable", JoinKind.LeftOuter),
    #"Expanded maxTable" = Table.ExpandTableColumn(#"Merged Queries", "maxTable", {"Max"}, {"Max"}),
    #"Grouped Rows" = Table.Group(#"Expanded maxTable", {"Group"}, {{"allrows", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each 
[a=Table.AddIndexColumn([allrows], "Index", 0, 1, Int64.Type),
b=Table.AddColumn(a, "RT", each List.Last( List.Accumulate( List.FirstN( a[Value],[Index]+1),{},(x,y)=>x&{{y, if List.Last(x,{0,0}){1}+y>[Max] then List.Last(x,{0,0}){1} else List.Last(x,{0,0}){1}+y}})){1}),
c=Table.AddColumn(b, "Verifier", each if [Index]=0 then 1 else if [RT]<>b[RT]{[Index]-1}+[Value] then 0  else 1)][c]),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Group", "Value", "RT", "Verifier"})
in
    #"Expanded Custom"

I have maxTable

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci/KLy1Q0lHyTaxQitWJVnIEsk3MwUwnENMSzHQGMg2NDJRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Group", type text}, {"Max", Int64.Type}})
in
    #"Changed Type"

 

Both Queries you can paste in Advanced Editor via blank query, if you change table name, modify the merge maxTable accordingly.

RT is an adpotion of original post here: Running total that outputs "0" if the sum is negat... - Microsoft Power BI Community

 

Next time, provide your sample data in a format which people can copy.

View solution in original post

1 REPLY 1
Vera_33
Resident Rockstar
Resident Rockstar

Hi @DiogoOishi 

 

Here is one way, merge the maxTable to get Max value for each Group, then add RT and Verifier

Vera_33_0-1635913748273.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc2xDQAhDAPAXVJTgBNE/f9jIPZfA1Aku/nu5NjKnPZYsbBVUq2TTmHoDrGpkN33ppWE6CqAivz13RXVQ6TCScfP6Ght", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Group = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Group", type text}, {"Value", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Group"}, maxTable, {"Group"}, "maxTable", JoinKind.LeftOuter),
    #"Expanded maxTable" = Table.ExpandTableColumn(#"Merged Queries", "maxTable", {"Max"}, {"Max"}),
    #"Grouped Rows" = Table.Group(#"Expanded maxTable", {"Group"}, {{"allrows", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each 
[a=Table.AddIndexColumn([allrows], "Index", 0, 1, Int64.Type),
b=Table.AddColumn(a, "RT", each List.Last( List.Accumulate( List.FirstN( a[Value],[Index]+1),{},(x,y)=>x&{{y, if List.Last(x,{0,0}){1}+y>[Max] then List.Last(x,{0,0}){1} else List.Last(x,{0,0}){1}+y}})){1}),
c=Table.AddColumn(b, "Verifier", each if [Index]=0 then 1 else if [RT]<>b[RT]{[Index]-1}+[Value] then 0  else 1)][c]),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Group", "Value", "RT", "Verifier"})
in
    #"Expanded Custom"

I have maxTable

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci/KLy1Q0lHyTaxQitWJVnIEsk3MwUwnENMSzHQGMg2NDJRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Group", type text}, {"Max", Int64.Type}})
in
    #"Changed Type"

 

Both Queries you can paste in Advanced Editor via blank query, if you change table name, modify the merge maxTable accordingly.

RT is an adpotion of original post here: Running total that outputs "0" if the sum is negat... - Microsoft Power BI Community

 

Next time, provide your sample data in a format which people can copy.

Helpful resources

Announcements
Europe Fabric Conference

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.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors