Are you looking to create Running Totals by Category in Power Query? In this article, we’ll show you a step-by-step method to compute a grouped running total by using the powerful List.Generate function and the Group by feature. Say goodbye to manual calculations and hello to a streamlined process.
In my previous post, I shared effective methods to calculate a running total, but how can you apply this logic to groups? And make sure the performance is good, too?
We will first look at how to transform running total logic into a function. You will then invoke this function to compute a running total by category.
Table of contents
1. What is a Grouped Running Total
A grouped running total is a running total that resets for each group in your dataset. How does that work?
To start out, a running total is a way to keep track of a sum of numbers as they change over time. Essentially, it is a cumulative total of a series of numbers, where each new number is added to the previous total.
Now, when it comes to creating a running total that resets for every unique group in your dataset, it adds an exciting new angle. A grouped running total allows you to see how each individual group is performing separately. For example, let’s say you’re tracking sales by region, if you create a running total that resets for every region, you can see how each region’s sales are progressing over time, instead of just seeing the overall sales for all regions combined.
It would mean a running total with a condition, which is the equivalent of the OVER and PARTITION BY clause in SQL.
2. Running Total
Let’s first recap how to create a running total and turn this code into a function.
2.1. Create a Running Total
You can calculate a running total in different ways, as I showed here. For this post, we will go with the fast List.Generate version. After all, who wants a slow query when you can have one with good performance?
My other article starts with a query called Sales.
The running total query then:
- loads the values from the Amount column into memory with the List.Buffer function.
- generates a list with running total values with List.Generate.
- combines the original table with the list of Running Total values.
You can find all this in below code:
let
Source = Sales,
BuffValues = List.Buffer( Source[Amount] ),
RunningTotal =
List.Generate (
() => [ RT = BuffValues{0}, RowIndex = 0 ],
each [RowIndex] < List.Count(BuffValues),
each [ RT = List.Sum( { [RT] , BuffValues{[RowIndex] + 1} } ),
RowIndex = [RowIndex] + 1 ],
each [RT] ),
#"Combined Table + RT" =
Table.FromColumns(
Table.ToColumns( Source )
& { Value.ReplaceType( RunningTotal, type {Int64.Type} ) } ,
Table.ColumnNames( Source ) & {"Running Total"} )
in
#"Combined Table + RT"2.2. Turn Logic into a Function
For the running total by group, we want to use before logic on multiple tables. The easiest way is to turn this code into a function. To do that, you:
- define your variables at line 1
- make a reference to your table parameter (MyTable) at lines 3, 14 and 16
- Return a list of values by using the Table.Column function with 2 parameters at line 4
You will end up with below code:
( RTColumnName as text, MyTable as table, ValueColumn as text) =>
let
Source = MyTable,
BuffValues = List.Buffer( Table.Column( MyTable, ValueColumn ) ),
RunningTotal =
List.Generate (
() => [ RT = BuffValues{0}, RowIndex = 0 ],
each [RowIndex] < List.Count(BuffValues),
each [ RT = List.Sum( { [RT] , BuffValues{[RowIndex] + 1} } ),
RowIndex = [RowIndex] + 1 ],
each [RT] ),
#"Combined Table + RT" =
Table.FromColumns(
Table.ToColumns( MyTable )
& { Value.ReplaceType( RunningTotal, type {Int64.Type} ) } ,
Table.ColumnNames( MyTable ) & { RTColumnName } )
in
#"Combined Table + RT"Paste this code into the advanced editor and name your query: fxRunningTotal.
3. Running Total by Category
Knowing all this, it’s now time to create a running total by category.
3.1. Summarizing Data
In the next step, you summarize the data by category using the Group By feature in Power Query. This step is required so your running total runs separately for each group.
You need to prepare the dataset at the level you want the running total at. At the same time, you need to make sure no data is lost in the Group By operation.
To do that:
- Go to the Home tab in the ribbon and select Group By
- Select Product as Group By Column
- Perform the All Rows operation to condense the summarized rows into a table object per row.
The result of this operation is a unique row for each Product and a table object that contains all rows relating to the product summarized.
3.2. Apply Function on Groups
So, how can we compute a running total by category from this situation? The key to doing this is in the Table.Group function used for the Group By Operation. The third argument is the aggregatedColumns argument.
You can either provide a reference to a single column or retrieve all summarized rows. In the example, the All Rows operation retrieves all summarized rows by using the underscore: _.
= Table.Group(
#"Sorted Rows",
{"Product"},
{ { "Details",
each _,
type table [ Date = nullable date, Product = nullable text,
Amount = nullable number ]
} } )So the _ returns us a Table Object, and we just created the fxRunningTotal function that runs against a Table. Put these together, and you can compute your running total by wrapping the fxRunningTotal function around the _. Don’t forget to specify the new name for the Running Total column and the name of the value column for computing the running total.
= Table.Group(
#"Sorted Rows",
{"Product"},
{ { "Details",
each fxRunningTotal( "Running Total", _, "Amount" ),
type table [ Date = nullable date, Product = nullable text,
Amount = nullable number ]
} } )
After running the code, each table object in the Details column now contains a Running Total column. You can preview the data by clicking on the white space in a cell that contains a table object.
3.3 Include Field and Data Type
You’re almost done now, but one issue remains. When you try to expand the Details column, the new Running Total column is missing.
The issue is in the last argument of Table.Group. You find a specification of the column types of each column, but the Running Total column is missing.
To fix this, add the Running Total column with its data type.
You can now expand the columns and see the result of your Running Total by Category.
The example in this post shows how to create your running total by Product, but you can use it in all kinds of ways. If instead, you group your data by Year and Month, you would have a running total by month.
You can easily adjust your running total grouping (or condition) to support week, quarter, year, etc.
3.4. Consolidate Steps into Function
We build up the previous steps chronologically so it’s easier to understand how to compute a running total by category. And as you can see, it’s still quite a few steps.
If you are only interested in the results, you can also use the below function that combines the logic for both the running total and the summarizing and expanding of the data.
let func =
( RTColumnName as text, MyTable as table, ValueColumn as text, GroupByColumns as list ) =>
let
Source = MyTable,
//---------- RunningTotalFunction ----------
fxRunningTotal = ( RT_ColumnName as text, RT_Table as table, RT_ValueColumn as text) =>
let
Source = RT_Table,
BuffValues = List.Buffer( Table.Column( RT_Table, RT_ValueColumn ) ),
RunningTotal =
List.Generate ( () => [RT = BuffValues{0}, RowIndex = 0],
each [RowIndex] < List.Count(BuffValues),
each [ RT = List.Sum( { [RT] , BuffValues{[RowIndex] + 1} } ),
RowIndex = [RowIndex] + 1],
each [RT]
),
#"Combined Table + RT" =
Table.FromColumns(
Table.ToColumns( RT_Table )
& { Value.ReplaceType( RunningTotal, type {Int64.Type} ) },
Table.ColumnNames( RT_Table ) & { RT_ColumnName } )
in
#"Combined Table + RT",
//---------- END RunningTotalFunction ----------
// Applying function to GroupBy Operation
#"Grouped Rows" =
Table.Group(
Source,
GroupByColumns,
{ {"Details", each fxRunningTotal ( RTColumnName, _, ValueColumn), type table } }
),
#"Removed Other Columns" =
Table.SelectColumns(#"Grouped Rows",{"Details"}),
#"Expanded Details" =
Table.ExpandTableColumn(#"Removed Other Columns", "Details",
Table.ColumnNames( Source ) & { RTColumnName },
Table.ColumnNames( Source ) & { RTColumnName } ),
RestoreDatatypes =
Value.ReplaceType(
#"Expanded Details",
Value.Type( // Creates dummy column to retrieve data type
Table.AddColumn(
Source,
RTColumnName,
each null,
Int64.Type
)
)
)
in
RestoreDatatypes,
documentation = [
Documentation.Name = " fxRunningTotalByCategory ",
Documentation.Description = " Adds a running total column to a table, based on a value column and one or more group by columns.",
Documentation.LongDescription = " This function adds a running total column to a table. For the running total you can specify the columns over which the running total should run. When you specify Year and Month as GroupByColumns, the running total will reset for each change in year and month in your dataset. It's important to sort your data in the desired order before you use this function. ",
Documentation.Category = " Running Total ",
Documentation.Source = " BI Gorilla â https://gorilla.bi ",
Documentation.Version = " 1.0 ",
Documentation.Author = " Rick de Groot ",
Documentation.Examples = {[Description = " ",
Code = " let
RTColumnName = ""Running Total"",
MyTable = Source,
ValueColumn = ""Amount"",
GroupByColumns = { ""Product"", ""Color""},
Result = fxRunningTotalByCategory( RTColumnName, MyTable, ValueColumn, GroupByColumns)
in
Result ",
Result = " Source Table that includes a running total by Product and Color
"]}]
in
Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))Save this code as a separate query, and you can start using the function. Let’s say you have a table where the Amount column contains your values, and you want to create a running total column called Running Total and compute it by Color and Product. If you call this function fxGroupedRunningTotals, you can use it like this:
= fxGroupedRunningTotals( "Running Total", TableName, "Amount", { "Color", "Product" } )
// Returns a table with a running total column by color and product.
= fxGroupedRunningTotals( "Running Total", TableName, "Amount", { "Product" } )
// Returns a table with a running total column by product.
= fxGroupedRunningTotals( "Running Total", TableName, "Amount", { } )
// Returns a table with a running total column4. Download
To see the code in action, you can download the accompanying file below.
5. Conclusion
So, there you have it! You now know how to create a running total by category in Power Query using the List.Generate function and the Group by feature.
We started by creating a function for a regular running total. Then, I took you down the road of grouping your data and including the function in the All Rows operation. The last step involved including the Running Total at the type argument in Group By. And that’s how you arrive at a running total by group.
Looking for a regular running total? Check out this article.
Enjoy Power Query!
Recommended Reading…
Power Query Foundations
Beginners Guide to Power Query
List Functions (200+ Examples)
Text Functions (150+ Examples)
Creating Tables (40+ Examples)
Generate the Ultimate Date Table
Advanced Topics
Master List.Generate with Easy Examples
Deep Dive into List.Accumulate
Thanks for your blog articles, it helped me a lot !
I made an alternative version which identify before hands rows where running totals is reset. Before grouping I add a index to have grouping giving minimal index for each group. Then I transform it to a buffered list and use it to add or not previous running total.
Don’t know with a lot of data what is the most optimized. On my limited set it looked like it was the same. I also saw @StÊphane solution in comments for “Create Running Totals in Power Query M (Ultimate Guide)” which I tested and this one is definitely a lot slower.
Here my function :
After some test method to combine table take a lot of times imho.
I tested with a table of about 500 000 lines. Refresh was so long that I stopped it after 20 minutes.
I changed the way to retrieve running total inside the original table using Table.AddColumn and fetching for each row the corresponding value from running total list. Now it takes 8 minutes to run, still long but at least it finishes.
My new code (hoping it will format it correctly this time !?) :
`
Hi Rick,
Thanks for great video and article!
I tried to filter on som dates after expanding the columns, step 3.3 with hope that it should run totals on selected dates only. It did not. What part should be dynamic to get wanted result?
Can you clarify your desired result? I’m not entirely sure what it is you want to achieve.
Hi Rick,
Great series, extremely helpful. I was going through the running totals video and it has already helped in streamlining my process. Just wanted to check if you have any suggestion for my one issue. So I have Targets that are populated at weekly level however they are just duplicates as we have only Month level Targets. While creating the running total I need to get them on month level by just taking the average of week level Targets and add them on month level.
Would be great to know your thoughts on this one!
Hi Venkatesh,
Sorry, it’s not entirely clear to me what you want for distributing the targets.
Hello Rick,
First and foremost I would like to thank you for your videos that are easy to follow and explain in detail what the individual lines do! This helped me tremendously!
I’ve been trying to calculate the amount of different currencies held in a specific portfolio at any given time. I could extract an Excel file of all the movements but it didn’t have a column showing the total currencies held. So I applied the different steps laid out by you and it works great! The only issue I have is that some of these currencies were exchanged between each other(i.e. 50âŦ exchanged to 50$), these exchanges are referenced by one column listing the input currency, another the input currency amount, and the same for the output currency.
So I was thinking of simply adding a Running Total column for the Input Amount Currency and then using a Lookup function to
subtract from the Output Amount Currency. Is there a better way to do this in one go? Maybe a running total that adds and subtracts based on two different columns?
Thank you again for your helpful work!
Hey Arnaud,
This sounds like a great candidate to perform in DAX. Just have your values on each moment with the local currency. Then use some DAX to lookup the conversion.
You could also convert your values on a date level if you have the exchange rates for each. Perhaps merging a lookup table based on currency + date would be easiest.
I have spent years trying to figure out “Grouped Running Totals” in Power Query. I have visited this site many times, but it never clicked.
Today I watched the video (I usually just read the text) and everything made sense. This is so much easier than I thought, and I also learned a bit about functions.
I recommend that anyone who is struggling watch the video – so helpful. Thanks!
Hi Rick,
I’m on a third watch of this video and it just occurred to me…do we need the sort on the product? Doesn’t the Group By take care of that for us?
Hey Deron,
Thanks for reaching out! I understand what you’re saying about the Product sort being unnecessary when you group your data by Product. It’s a great point and I appreciate you bringing it up.
When you group your data on Product, most likely the Product sort is unnecessary. After all, you will only apply the function on the rows belonging to that group.
Hello Rick,
I wonder want to know how to sum amount base on max date or buttom up,
I try to get change below sytanx in power query, but it didn’t work maybe there thing I miss. Please help me to direct the right way, Thanks!
I think I made it work, but unfortunately i think i not really know about list.generate this fun.
Hi Rick,
Your detail documents and videos are the best of the best.
By the way, I have bank statements which have many transaction dates, and also different columns for Debit Amount, and Credit Amount.
I need to find a way to produce Running Total of Debit Amount Column, and another Running Total for Credit Amount Column.
Then I will be able to produce daily closing balance.
Thank you in advance.
Sovan – you could create a running total (using list.generate) that computes a debit column, and one that creates a credit column.
Those would be 2 separate statements.
And for the credit one you would add an if condition to only deduct a value when it’s < 0. For the debet one the opposite. Finally you would have to add 2 columns to the original table, not one. It can be done with similar logic đ Cheers, Rick
Hi Rick,
Your blogs have been extremely helpful! Especially your two running total ones. I was wondering, is there was a way to restart a running total after it hits a certain value? I am tracking values that are going to be adding together and should be negative, but if the running total goes above 0, I want that value to be shown in the row it happens and the next row to start running total over. Is there a way to do this?
Blake – You should be able to add the running total logic you describe in List.Generate’s 3rd argument. It would require checking whether the RT value is >= 0.
Something like:
Then depending on what you want to return (do you want to return everything that is > 0 in the new running total, or just discard it).
It would all be the conditional logic. It sounds quite a challenge. I don’t have a ready made example for you cooked up, but hope you can play around with it.
Hi Rick & Blake,
Thank you very much Rick, extremely helpful. I have a similar request to Blake except I want the Running Total to reset to 0 when the Amount is 0. My Use Case is I am calculating the number of consecutive working day shifts for each employee, but once the employee does not work the shift that day then the Running Total should reset to 0. Would you please show me how to do this?
Hi Warren. I would suggest an alternative approach.
If you sort your data in the correct way (probably datetime and employee), you can group your data by EmployeeID using GroupKind.Local. Also see:
https://powerquery.how/groupkind-local/
Then you can add an index column to each of those groups shown here:
https://www.youtube.com/watch?v=_PBX3RPXxHw
In that way you don’t need a complex List.Generate statement.
However, I understand there’s more people having the need to reset a list.generate statement based on a statement. I’ll consider writing an article on it.
Cheers, Rick
Hi Rick,
Thank you for your feedback. I applied the changes you suggested and hasn’t quite solved the challenge I face. Please assume in my data I have 30 days of shifts for each employee. With your changes I now have an index starting from 1 – 30 for each employee which is great. If employee#1 worked days 1 – 3, the “Consecutive Day Shift Count” is correctly showing the running total count of 3 on day 3. However, on day 4 the employee did not work (“Shift Count” = 0) so instead of resetting the count to 0 on day 4; the “Consecutive Day Shift Count” is still showing the previous running count of 3. It will stay at 3 until the next working shift when it moves to 4. My aim is to reset the “Consecutive Day Shift Count” to 0 on Day 4. Then on day 5 if the employee works again the “Consecutive Day Shift Count” should be 1. I hope this helps explain my challenge. I thank you again for you help.
Can it be that you haven’t applied GroupKind.Local? The issue you describe happens when applying GroupKind.Global.
If you add a flag that says: Hasworked (true or false) and include that in the grouping, then the consecutive series is captured by Groupkind.Local. This means the running index resets for each series.
Hi Rick,
Could you please let me know if this would work for a running total across 2 or more categories? For example, to create a running total by product and month, would I need to create individual product tables and then compile after creating the running total calc?
Thanks
Yes, because you can Group By more than one column. In my case I sorted and then grouped by six different columns. It all worked a treat!
Hi Joe,
Chris is right. You can perform the Group By operation on any number of columns you want (in your case on Product and Month).
Then call the function on the table objects that have been summarized on that level, and expand.
You should then have your desired results đ
Hi Rick,
Thank you for you great job!
One minor thing: is there some logic behind your parameters order?
Just in all powerquery functions that I know (Table.AddColumn, for instance), the first argument is TableName (in 99% cases, it’s previous step name), and the ColumnName is the second, not vice versa.
Hi Denis,
Fair question to ask. I didn’t give the order of the arguments too much thought really. Maybe I should from now on, thanks for pointing that out. Please feel free to adjust it to your liking. đ