Compute a Running Total by Category in Power Query

This post shows how to calculate a running total by category in Power Query. A running total by category computes the cumulative sum but only for values within the specified group your current row belongs to.

You may for instance be interested to only calculate a running total for all products that fall within the same category. When your calculation arrives at a new product category, the running total then resets.

It would mean a running total with a condition, which is the equivalent of the OVER and PARTITION BY clause in SQL.

My previous post already showed how to calculate a running total, so how can you apply this logic to groups? And make sure the performance is good too?

That’s what this post is about. We will first look how to transform running total logic into a function. You will then invoke this function to compute a running total by category.

Create a Running Total

You can calculate a running total in different ways as I showed here. And for this post we will go with the fast List.Generate version. After all, who wants a slow query when you can have a one with good performance?

My other article starts at a query called Sales.

Running Total Dataset

The running total query then:

  1. loads the values from the Amount column into memory with the List.Buffer function.
  2. generates list with running total values with List.Generate.
  3. 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"

Turn Running Total into 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:

  1. define your variables at line 1
  2. make a reference to your table parameter (MyTable) at line 3, 14 and 16
  3. 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.

Running Total by Category Function

Summarize Data by Category

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 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 -> 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.
Summarize data by Category

The result of this operation is a unique row for each Product and a table object that contains all rows relating the product summarized.

Compute a Running Total by Category

So how can we compute a running total by category from this situation? The key for doing this is in the Table.Group function used for the Group By Operation. The third argument has 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 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]
  } }
)
Running Total by Category in PQ

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.

Adjust Group By Formula

You’re almost done now, but one issue remains. When you try to expand the Details column, the new Running Total column is missing.

Missing Running Total Column

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.

Type Table Argument

You can now expand the columns and see the result of your Running Total by Category.

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.

And you can easily adjust your running total grouping (or condition) to support week, quarter, year etc.

Function for Running Total by Category

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

= 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 column

Summary

This post showed you the steps to create a Running Total by Category. It started by creating a function for a regular running total.

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

To see the code in action you can download the accompanying file below.

Enjoy Power Query!

Recommend Reading

>>> Power Query - Foundations <<<
 List Functions (200+ examples)
 Text functions (150+ examples)
Creating Tables from Scratch (40+ examples)
 Understanding If Statements 
Generating a Date Table


>>>  Power Query - Advanced Topics <<<
Master List.Generate with Easy Examples
 Deep Dive into List.Accumulate
Create Calendar with Dynamic Language
Replacing Values (User Interface and Beyond)
Share on:
About Rick de Groot

Rick is the founder and editor of BI Gorilla. He believes learning is one of the great pleasures in life and wants to share his knowledge to help you improve your skills.

Learn more about him here, connect with him on Twitter, Facebook and LinkedIn and subscribe to his YouTube Channel.

7 thoughts on “Compute a Running Total by Category in Power Query”

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

    Reply
  2. 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?

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

    Reply
    • 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!

      Reply
    • 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 😁

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

    Reply
    • 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. 😁

      Reply

Leave a comment