Are you looking to create Running Totals by Category in Power Query? This article guides you through the process, step by step. We’ll use the List.Generate function and the Group By function to compute a grouped running total and make sure your calculations are both accurate and efficient.
If you’ve read my previous article on calculating running totals, you might be wondering how to extend that logic to categories. And how do you ensure good performance while doing so?
We’ll start by turning the running total logic into a reusable function. Then, we’ll use this function to calculate running totals for each category in your dataset.
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.
First, let’s clarify what a running total is. 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, imagine you’re tracking sales by region. A grouped running total would show you the sales for each region separately, resetting the total for each new region. This way, you can monitor how sales are evolving in each region, rather than just looking at the overall sales figure.
In technical terms, a grouped running total is like applying conditions to your running total. If you’re familiar with SQL, it’s similar to using the OVER and PARTITION BY clauses.
2. Running Total
Let’s start by revisiting how to create a basic running total. Then, we’ll adapt this into a reusable function.
2.1. Creating a Basic Running Total
Calculating a running total can be done in various ways. For the purpose of this article, we’ll focus on the List.Generate method for its speed and efficiency.
My other article starts with a query named “Sales”.
In the Running Total query, you’ll do the following:
- Use the List.Buffer function to load the values from the “Amount” column into memory.
- create a list that contains the running total values using List.Generate.
- Merge this list with your original table to include the 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. Converting the Logic into a Function
If you want to calculate running totals for different groups, it’s useful to convert this logic into a function. Here’s how:
- Start by defining your variables at the beginning of the code.
- Reference your table parameter, which we’ll call “MyTable,” throughout your code.
- Use the Table.Column function to return a list of values.
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
Understanding how to create a basic running total is a start, but what if you want to track running totals within specific categories? This chapter will guide you through the process.
3.1. Summarizing Data
The first step is to organize your data by the categories you’re interested in. In Power Query, you can do this using the Group By feature.
- Navigate to the Home tab and click on Group By.
- Choose the column that represents your category, such as “Product.”
- Use the “All Rows” operation to summarize all the rows related to each category into a table object.
This will give you a unique row for each category, along with a table object containing all the related rows.
3.2. Understanding the Table.Group Function
Once your data is grouped by category, the next step is to apply the running total function to each of these groups. This is where the Table.Group function comes into play.
The Table.Group function is important for applying the running total to each category. Specifically, the third argument in this function, known as aggregatedColumns, is where the magic happens.
In our example, we use the “All Rows” operation, represented by an underscore (_), to collect all rows related to each category.
Here’s what the code looks like initially:
Table.Group(
#"Sorted Rows",
{"Product"},
{ { "Details",
each _,
type table [ Date = nullable date, Product = nullable text,
Amount = nullable number ]
} } )3.2. Applying the Running Total Function
We’ve already created a function called fxRunningTotal that calculates a running total for a table. To apply this function to each category, wrap it around the underscore (_) in the aggregatedColumns argument.
Also, specify the name you want for the new Running Total column and the column that contains the values for calculating the running total.
We can also remove the data types specified, as the technique we use to combine the tables won’t need it.
Here’s how to modify the code:
Table.Group(
#"Sorted Rows",
{"Product"},
{ { "Details",
each fxRunningTotal( "Running Total", _, "Amount" ),
type table
} } )
After executing this code, you’ll find that each table object in the “Details” column now includes a Running Total column. To see this in action, click on the white space in a cell containing one of these table objects.
3.3. Finalizing and Combining Your Data
Typically, your next step would be to expand columns. However, instead of expanding the column, you can also use the Table.Combine function. This approach offers two main advantages:
- It avoids hardcoding column names.
- It automatically picks up the underlying data types of the columns.
The Table.Combine function requires a list of tables. Fortunately, we already have a column, “Details,” that contains table objects for each category. To create a list of these tables, you can use a column reference, also known as field selection.
If your last step was named “GroupedRows,” the following statement will create the list:
GroupedRows[Details]This list contains tables that have been summarized and include the running total values.
To merge these tables into one, use the following code:
Table.Combine( GroupedRows[Details] )After executing this code, you will find the columns expanded and including your Running Total by Category.
The method described here focuses on creating a running total by “Product,” but it’s flexible. You can easily adapt your Group By operation to categories like “Year and Month” to get a running total by month, or by “Week,” “Quarter,” or “Year.”
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
GroupedRows = Table.Group(
Source,
GroupByColumns,
{{"Details", each fxRunningTotal( RTColumnName, _, ValueColumn), type table }}
),
CombineData = Table.Combine( GroupedRows[Details] )
in
CombineData,
documentation = [
Documentation.Name = "fxGroupedRunningTotals",
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.1 ",
Documentation.Author = " Rick de Groot ",
Documentation.Examples = {[Description = " ",
Code = " let
RTColumnName = ""Running Total"",
MyTable = Source,
ValueColumn = ""Amount"",
GroupByColumns = { ""Product"", ""Color""},
Result = fxGroupedRunningTotals( 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:
// Returns a table with a running total column by color and product.
fxGroupedRunningTotals( "Running Total", TableName, "Amount", { "Color", "Product" } )
// Returns a table with a running total column by product.
fxGroupedRunningTotals( "Running Total", TableName, "Amount", { "Product" } )
// Returns a table with a running total column
fxGroupedRunningTotals( "Running Total", TableName, "Amount", { } )4. Download
To see the code in action, you can download the accompanying file below.
5. Conclusion
And there you have it! You’ve learned how to calculate a running total by category in Power Query. We’ve covered everything from using the List.Generate function to using the Group By feature.
We began by creating a function to calculate a basic running total. From there, we delved into grouping your data and incorporating this function within the All Rows operation. Finally, we tackled how to combine the data back together. This comprehensive approach allows you to create running totals tailored to specific groups in your data.
Tip
If you’re interested in further expanding your Power Query toolkit, you might want to explore how to create running total columns in bulk.
Enjoy Power Query!
Edit 05-10-2023: Improved function code to combine tables using Table.Combine.
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
Brilliant post! I recently purchased your book and this is a very nice addition.
A follow up question would be, what are the optimised steps for including cumulative % by category, also?
Thanks đ
Hey Rick, I loved your videos and I had learned a lot from them.
I’m using the your fx to calculate the RT, but I noted something weird after run the RT and merge another query and expanded the info needed from that query; the values on my RT colunm changed.
I took a row to verify it, and the RT value before the merge was 7 and after the merge was 2415, what change all my results. Do you know why it is happening?
I look on the web and they said that is needed the Index to keep the values.
Hey Rafael,
That sounds odd. Power Query does not guarantee the order of your values in many operations. Could it be that when you order your data (after the merge) the values are actually correct?
In case you are sure it does not compute the right values you can resort to Table.Buffer and/or Table.Sort before applying the running total
I’m using the Table.Sort before to RT, the issue is after the RT I have to Merge the table again to bring data based on a calculation after the RT; and repeat the process 15 times. I’m creating a modelling to project the completion date of 44k orders using a limited capacity by each group. Do you know a better way to do it?
Hello Mr. Rick.
First of all, honestly thank you very much for your insightful article. I’ve spent a bunch of time to realize and internalize these code and the results were great. I’ve tried many ways and finally I can understand your codes.
But during my journey, I got a question. how should I change the code if I don’t want to call it as a function inside GroupBy? Indeed, I am curious to know if we can use RT Formula directly into GroupBy Function.
Best Regard
Hi Rick,
Thanks a lot for all your great posts and videos.
One suggestion for this Running Total by group function : it would be great if the function could also add an index column in the table at the “Group by” step.
I had to do further merges after the RT calculation and it changed the rows order, making the RT look erratic. An index column would make it super easy to reset the rows in the proper RT order.
Hi Rick,
Thank you for sharing such insightful blog. It is tremendously helpful to newbie like me.
Coming from Tableau background, I am struggling to find optimal performance solutions.
Can you recommend, how to modify the same use-case as provided in your example, running total for each product and date combination but with the condition that for each such combination, the running total column should be the cumulative of the previous 30 days (or maybe dynamic) value with respect to the current date value?
Thank you,
Ashish
Hi Rick, thank you so much for those explanations! they are really clear!
I am really struggling to decrement stock to allocate to orders on a SKU level.
I put the running totals, , but I cannot wrap my head around make the stock decremented by orders (considering orders are already sorted). please please please could you help me ????
thank you!
What exactly is it that you want to achieve? It’s not completely clear to me.
Hi Rick,
Very helpful post and video. But I tried your code and it did not work on my data, until I figured out that the order of the columns is important. It only works when you put the Date as the first column of the table. It has probably to do with how the “group by” code works.
Anyway, thanks again!
Hi Maarten,
I had the same problem and tried your solution and it works.
So, your comment was very useful.
Thanks!
Hi Rick,
Thank you for this incredibly helpful blog. Especially the step-by-step explanations in videos and text helped me to get started and to understand.
Have you perhaps already created an explanatory video/blog on how I can deal with data where the periods are filled unevenly? For example: Product A was sold in Jan,Feb,Mar, Product B only in Jan, Feb. If I now filter my table for March, I am missing the RT of Product B.
I probably need to create and link a full calendar for all periods in the background beforehand, but unfortunately I don’t know how exactly.
Many thanks in advance
Hey Daniel,
Thank you very much. Regarding your question, that is a common requirement. I don’t currently have a video on it, but if you need a running total by month, this is the general process:
1. Create a list of unique month-year combinations from the start to the desired end period you require. Isolating the column from a calendar, and removing duplicates works great.
2. Summarize your main transactions table by month-year combinations. If this table has only a date, add a month-year column, and then group your data. Make sure to SUM the underlying values in the group by operation.
3. Starting from the values from step 1, merge the values from step 2 with this table. You end up with a table that has all combinations for both products and year-months.
4. Perform the running total.
It’s a bit of a hassle in power query. Depending on your requirements and your host system, you could also do this in DAX.
Rick
Hi Rick,
Thank you for the clear video! I am using this for several workflows. I followed your instructions to create the function and when trying to invoke it on a specific one, I receive the following error:
An error occurred in the ââ query. Expression.Error: The field ‘Amount’ already exists in the record.
Details:
Name=Amount
Value=
If I rename the column to “Amount2” beforehand as a test, it says the same error for the new column name. I appreciate any assistance.
Hi Marco – what parameters did you fill in for the function? The first argument provides the name of the running total column the function creates. Did you perhaps fill in ‘Amount’ there, although you already have a column named ‘Amount’?
Hi Rick,
After taking another look in your detailed written steps, I noticed that the parameters were renamed.
( RTColumnName as text, MyTable as table, ValueColumn as text) =>
Instead of
/( RT_Name as text, MyTable as table, RT_ColumnName as text) =>
I rewrote everything and this time I got the desired result. It seems I had a syntax error in the declarations that was not showing up and caused a loop. All good now. Great video!
Hi Rick,
Thank you for sharing this knowledge. Running totals have always been of great challenge and you’ve simplified it to a degree that is relatable and manageable to all. I had quite a complex operation to achieve with multiple “Grouped By” s and and a number of running totals (with if statements incorporated when running total reached a negative number there would be a reset to 0 and running total would continue).
Chantal – That’s great to hear! And I agree it’s not an easy process at all. Hope they do something to improve this in the future.
you could improve the code by using Table.Combine,
namely you could replace:
by
That’s a very good addition, also for respecting the data types. I have adjusted the article to include it. Thanks!
Rick
Hello Rick, thank you for this video it’s been enlightening.
Do you have any suggestions for summarizing two running totals at once? (Example: year to date budget and actuals) The buffer step doesn’t allow for multiple columns, should I repeat this process for the second column and merge the table results? Still new to PQ, but based on your video I’m guessing there’s a better solution.
Hey Mark.
That’s a good question. You can actually use a similar technique to create multiple columns. I figured providing some ready-made code may be easiest. I just released this article to address the question:
https://gorilla.bi/power-query/creating-multiple-running-totals/
Rick
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. đ