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.


Download Practice Workbook


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

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.

Dataset

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.

Opening Power Query Editor

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

Opening Power Query Editor

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

Finding Index Number

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.

Finding Index Number

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

Finding Index Number

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.

=List.Sum(List.Range(#“Added Index”[Sales],0,[Index]))

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.

Adding Custom Column

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.

Excel Power Query Running Total By Group

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.

Group By option

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

Excel Power Query Running Total By Group

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.

Excel Power Query Running Total By Group

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

Excel Power Query Running Total By Group

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.

Excel Power Query Running Total By Group

Read More: How to Calculate Running Total in One Cell in Excel (5 Ways)


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.

Using the List.Accumulate Function

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.

Excel Power Query Running Total By Group Using the List.Accumulate Function

Eventually, you’ll get the following output.

Excel Power Query Running Total By Group Using the List.Accumulate Function

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.

Using the List.Accumulate Function

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.

Excel Power Query Running Total By Group Using the List.Accumulate Function

Read More: Quick Analysis Tool: Calculation of Running Total in Excel (4 Ways)


Practice Session of the Running Total by Group in Excel

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.

Practice Session

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

Practice Session


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

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo