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 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
#"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 (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**.

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.

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

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

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

**Calculate Debit Credit Running Balance Using Excel Formula (3 Examples)****How to Use Pivot Table to Calculate Running Total by Date in Excel****How to Keep a Running Balance in Excel (8 Methods)****Cumulative Sum in Excel If Condition Applied (6 Methods)****How to Calculate Running Balance Using Excel Formula (4 Ways)****Calculate Horizontal Running Total in Excel (3 Ways)**

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

Hello Michael,

Please specify your problem in detail or send the excel file via [email protected] email address.

Hi, MICHAEL V BERNOT!

Thank you for your query.

You have pointed out a good problem. To solve your problem remove the

last bracketfrom the givenCustom column formulain the article above.And, another thing, choose the

All Rowsoption in theOperationoption list instead ofCount Rowsin theGroup Bywindow.I hope this solves your problem. Stay with ExcelDemy for more Excel tips, tricks, formulas, and solutions.

Regards,

Tanjim Reza

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?

Hello DONNA ATKINS,

Thank you for your feedback, the answer to your question is provided in the steps below, so follow along.

Step 1.First, completesteps 1 through 5 from Method 1>> now, follow the steps shown in the live demonstration.Step 2.Next, click onClose & Loaddrop-down >> selectClose & Load tooption.Step 3.Lastly, choose theTable or PivotTableoption according to your preference >> load this data into a new worksheet.Hopefully, this solves your problem. Have a good day.