# How to Calculate Running Total by Group Using Excel Power Query

Get FREE Advanced Excel Exercises with Solutions!

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.

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

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.

`=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. 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
in

//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. ### 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.  ## 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 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.

1. Reply Michael V Bernot Sep 27, 2022 at 9:28 AM

The List.Accumulate method does not appear to be giving the correct running totals by the month.

• Reply Md. Abdul Kader Jan 25, 2023 at 4:23 PM

Hello Michael,

• Reply Hi, MICHAEL V BERNOT!

You have pointed out a good problem. To solve your problem remove the last bracket from the given Custom column formula in the article above.
And, another thing, choose the All Rows option in the Operation option list instead of Count Rows in the Group By window. I hope this solves your problem. Stay with ExcelDemy for more Excel tips, tricks, formulas, and solutions.

Regards,
Tanjim Reza

2. Reply Hi there

Once you have generated the running totals in each of the groups, which are accessed by clicking on the tables created for each group, how do you then combine all of these new tables into one big table so that you can access these running totals?

• Reply Hello DONNA ATKINS,

Step 1. First, complete steps 1 through 5 from Method 1 >> now, follow the steps shown in the live demonstration. Step 2. Next, click on Close & Load drop-down >> select Close & Load to option. Step 3. Lastly, choose the Table or PivotTable option according to your preference >> load this data into a new worksheet. Hopefully, this solves your problem. Have a good day. Advanced Excel Exercises with Solutions PDF  