Create a Running Total by Category in Power Query

Want a running total that resets for different categories? This video shows how you can create grouped running totals.

WRITTEN ARTICLE

Master Functions and Syntax in M

CONTENTS
00:00 Introduction
00:44 Running Total List.Generate
01:40 Create Running Total Function
06:36 Apply Running Total Function
08:26 Running Total by Group

ABOUT BI Gorilla:
BI Gorilla shares videos and articles on Power BI and Excel to help you improve your skills.

SUBSCRIBE TO MY CHANNEL

LET'S CONNECT:

Thank you for your support!

#RunningTotal #powerquery #bigorilla

[check]

This is SO complicated for such a simple calculation. Not criticizing the teaching here, which is very clear and helpful. Rather, I don't get why Microsoft doesn't just have a button for "calculate running total by group".

[SankalpSoni-it]

Hi, you have given a very nice explanation of running total.could you please help me in one problem that is,
i am plotting stack chart of running count of categories with respect to date and using the same categories column as legends but in last month i don't have particular category and this running count is not carry forwarding the previous month value. so how can i do it.

i am asking you this because this query is bit related to running sum and one loophole you mentioned in the last of one video.

[raitup]

I'm seeing the steps you are applying and everything makes sense, you even make it look very easy, but for me to reach that solution, I would have to have a privileged mind like yours, to have that logic and reach the solution. And not only do you teach us the solution, you expand our minds to apply what we have learned in other problems. Greetings from Mexico!!!

[DeronHuskey]

I started today with your post on List.Generate which led me to your running totals videos.

3 hours later and at least 30 clicks of the pause button and another 10 or so rewinds, I'm done.

All three videos were great.

Watching the evolution of running totals from List.FirstN to the function used to group by category really helped it sink in.

Thanks

[nunobelo]

Amazing!! 5 stars!! It helped me a lot, thanks

[williamarthur]

Great video, worth watching for the tip on using Table.Column, I'd given up on a table [Column],
as variables in function, I have been using this simpler version just invoked on the Grouped Column,
not as flexible but does work,
(RTF)=>
let
Custom1 = List.Generate( ()=> [ RT = RTF [Unit] {0} , Counter = 0],
each [Counter] < Table.RowCount( RTF ),
each [RT = [RT] + RTF [Unit] {[Counter] + 1 }, Counter = [Counter]+1 ],
each [RT] ),
Custom2 = Table.FromColumns(
Table.ToColumns(RTF ) & { Custom1 }, Table.ColumnNames(RTF) & {"RTC"} )
in
Custom2

[RodrigoCalloni]

Thanks for the video... I came from Tableau word and this can be all done with a click of a mouse... I can't believe Power BI/Query requires a 17 minute video for a simple table calculation... it blows my mind

[agelee]

Is it not doable by ALLEXCEPT and ALLSELECTED on visualization level? This is crazy huge workaround.

[Rachel-pkuh]

Works perfectly in power query editor but when I try to establish the same table on a report all values from running total column disappear. Any clue how to problem solve this?

[robertgreen]

Thanks a lot for this. I'm trying to invoke a calculation for rolling 4 quarters sum by group. But I'm getting stuck. My rolling sum function won't work. Any tips?

= (RS_Name as text, MyTable as table, RS_ColumnName as text) =>
let
Source = MyTable,
Bufferedvalues = List.Buffer(Table.Column(MyTable, RS_ColumnName)),
Custom1 = Table.AddColumn(Source, "Rolling Sum", each if [Index]>=4 then List.Sum(List.Range(Bufferedvalues, [Index]-4, 4)) else null)
in
Custom1

Cheers

[satyapal]

Hi, I am getting following error while applying function : Expression.Error: We cannot apply field access to the type Text.
Details:
Value=Running Total
Key=Details Please if you can help to get this solved
It was fine upto below step
= Table.Group(#"Changed Type1", {"Product"}, {{"Details", each _, type table [Month=nullable date, Product=text, TQty=nullable number]}})

Error in last step
= Table.AddColumn(#"Grouped Rows", "Custom", each Total"[Details], "Amount"))

[sasaandjani]

hai i tried to use your function, but why it said "Expression.Error: The name 'fxRunningTotal' wasn't recognized. Make sure it's spelled correctly". I already make sure that i wrote the function right. Do you have troubleshoot for this

[alpineagency]

Hi Rick, I‘m facing a problem right now.

After creating the function and trying to create the new column, my current table was brought back to its beginning look, so every step that was done before (name changing, type changing etc.) has been „removed“.

But the biggest problem I‘m facing is, that all columns, except the newly created one „running total“ have been moved down one row.

If I now use the function „Apply first row as header“ it tales the first value of „running total“ as the new header.

Is there a way to overcome this issue?

Thanks!!

[jimgleason]

Excellent as always. I love that you always tackle the more complex problems.

[robertgreen]

Very helpful thanks. However I need to calculate rolling 12 months by category. Can you help me to do that please?

[matthewgell]

Absolutely fantastic solution and so well explained. However I am struggling with it a bit with and 8 Million record fact table, I am currently experimenting with including a a buffered table right before the grouping - then referencing buffered table in the next two steps

[leetownsend]

Hi Rick, please can you give your advice on this. I note in the previous video, which i used the code for the start of this one you actually use the index function (can be seen in the advanced editor) rather than the count function that’s in the previous video. My data set is rather large (1.6m rows with a fair few steps before the buffered values step)and i am not getting the values i expect. Could it be that i should use index over counter and if so how can i apply. An updated version of this video would be amazing if there is now a better way. Thanks in advance.

[txreal]

Amazing! Thanks for explaining the logic that's easy to understand.
Like & Subscribe

[KaranAnand]

If I do a merge query(referencing another table to current) on an table after adding RT Column, then the RT column data changes to the Last RT balance of that category. Can you check?

[rofiemuhammad]

i found the result when grouping by date and one category is different with the result when grouping by date and two categories