How to Calculate Running Total by Group Using Excel Power Query

Running totals simply means the cumulative sum of a sequence of values. The application of calculating running total is quite significant for business purposes e.g. operating cash register, balancing transactions, and so on. However, you may easily compute the running total, particularly for a group of data using the Power Query, a data transformation and preparation engine in Excel. In this article, I’ll demonstrate you to how to calculate the running total by group using the Power Query feature in Excel with proper explanation.

Excel Power Query Running Total by Group: 2 Methods to Calculate

Let’s introduce today’s dataset where Sales of some Items are provided based on three months. Now, you need to calculate the running total by group (e.g. Month). That means if you choose a particular month, you’ll get the running total for that month only.

Now, let’s dive into the methods.

1. Running Total by Group

Initially, you’ll see the calculation of the running total first, then the application of the Group By option, and lastly, the running total by the group. Let’s enjoy the process in a step-by-step process.

Step 01: Opening the Power Query Editor 

Obviously, you need to open the Power Query Editor window to use the Power Query feature in Excel. Please do the below steps.

➤ Before opening the window, you need to select the dataset first and then choose the From Table/Range option from the Get & Transformed Data tab in the Data tab.

➤ Immediately, you’ll get the following window having the dataset.

Note: To avoid the repetitive task, you may duplicate Table1 (right-click over Table1 and choose the Duplicate option).

Step 02: Finding the Index 

If you want to measure the running total, you have to find the index. For finding it, go to Add Column tab > Index Column dropdown > From 1 option.

Then, you’ll get a new column having the index number for each Item.

Step 03: Determining Running Totals

At this stage, you have to determine the running total (not by group). For this, click on the Custom Column option from the Add Column tab.

Then, you’ll see the Custom Column dialog box. Next, rename the New column name as Running Totals and insert the following formula in the specified place as shown in the below screenshot.

Here, List.Sum function provides the sum of the values within it and List.Range function defines the range of Sales and it will change depending on the Index value.

Note: While inserting the formula for each time, make sure that No syntax errors have been detected is appeared.

So, the output will look as follows.

Step 04: Applying the Group By Feature

Now, apply the Group By option from the Home tab in the Transform ribbon. In the Group By dialog box, choose the Month from the drop-down list and All Rows from the drop-down list of the Operation.

After pressing OK, you’ll get the following grouped data.

Step 05: Calculating Running Totals by Group

In the previous step, you didn’t find any running totals in the grouped data. To find the running totals by group, you need to open the Advanced Editor from the View tab.

Insert the following code in the Advanced Editor dialog box.

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}, {"Items", type text}, {"Sales", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Month"}, {{"Count", each _, type table [Month=nullable text, Items=nullable text, Sales=nullable number]}}),

   //Function to Compute Running Totals 

   RunTotalFunction = (RunTotalTable as table) as table =>
   let 
    #"Added Index" = Table.AddIndexColumn(RunTotalTable, "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Running Totals", each List.Sum(List.Range(#"Added Index"[Sales],0,[Index])))
in
    #"Added Custom",

    //Assigning the Function
    RunTotals = Table.TransformColumns(#"Grouped Rows", {"Count", each RunTotalFunction(_)})
in
    RunTotals

Here, I created a function namely Runfunction to compute the running totals along with the declaration of the RunTable as a table. Then, I calculated the Index for the RunTable column and Running Totals (Also, you can copy the code from Table1 and type the RunTable instead of #”Changed Type”).

Subsequently, you need to call the function and assign the #”Grouped Rows” and “Count” for each Runfunction to determine the running totals.

However, you’ll get the final output. If you click on the Table of February month, you’ll get all information including the running total for that month.

Read More: How to Calculate Running Total in One Cell in Excel

2. Alternative Way: Using the List.Accumulate Function

Alternatively, you may calculate the running total by a group in Excel using the List.Accumulate function.

Step 01: Computing Running Totals First

Firstly, you need to find the index (discussed in Step 2 of the first method) for Table3.

Then, add a custom column namely Running Totals, and insert the following formula.

= List.Accumulate(
      List.Range(
            #"Changed Type"[Sales],
            0,
            [Index]),
      0,
      (state, current)=>state + current)

Here, List.Accumulate function accumulates the sum of values from the list. And the list is defined using the List.Range function of the Sales column.

Eventually, you’ll get the following output.

Step 02: Apply the Group By Option

Now, you need to apply the Group By option. Just choose the Month from the drop-down list.

Shortly, you’ll get the following running total by group (i.e. month). If you pick any Table from the Count table of any Month, you’ll get the running total for that particular month.

Practice Section 

If you want to practice with the used dataset, please download the .xlsx file from the Download Practice Workbook section. Then, go to Queries & Connections from the Data tab.

And click on Table1 on the right-side window of your download practice workbook.

Download Practice Workbook

Conclusion

That’s the end of today’s session. From the beginning to the end, I discussed the 2 ways to calculate the running total by group using the Power Query engine in Excel. So, I strongly believe this article will be highly beneficial for you. Anyway, if you have any queries or recommendations, please share them in the comments section below.

Related Articles

• Cumulative Sum in Excel If Condition Applied
• Calculate Horizontal Running Total in Excel
• How to Create Running Subtraction Total in Excel

<< Go Back to Excel Running Total | Calculate in Excel | Learn Excel