How to Calculate Running Totals in Power Query

Do you have year-to-date values and need to calculate running totals? What about if you have criteria you want to track running totals for? In this post, I’ll show you how to accomplish both and how you can create a custom function in Power Query that you can re-use in other queries. Here is a simple table I am going to use for this example:

Table in Excel showing sales by day, rep, and location.

In this table we have date, sales, salesperson, and location. Initially, I am just going to calculate the running total, regardless of the salesperson. I just want to know how much everyone has generated up to a certain point in time. The one step that I need to do is sort the dates in order, so they are going from smallest to largest. You can do this in either Excel or within Power Query.

Creating a running total for everything in Power Query

Step 1: Load your data into Power Query

To load your existing table into Power Query, click on your table and select the Data tab and click on From Table/Range.

Selecting the From Table/Range button in the Data tab.

Step 2: Sort your data

If your data is not already sorted in Power Query, then select the Date column and click the button to sot in ascending order.

Sorting data in Power Query.

Step 3: Create an Index column

On the Add Column tab, select the option to insert an Index Column, and select to the option to start From 1:

Creating an index column in Power Query.

This now creates a simple column that starts from 1.

A Power Query table with an index column.

Step 4: Create a column for the Running Total calculation

Select the Custom Column button from the Add Column tab, which will let you create a column based on a formula.

Selecting the Custom Column button from the Add Column tab.

There are two functions will be used in the formula. The first is the List.Sum function, which as the name describes, will sum up the values in a list. Within that will be the List.FirstN function which will grab all the index values up until that point. The custom column formula to enter, is as follows:

=List.Sum(List.FirstN(#"Added Index"[Sales],[Index]))

This is how it appears within the Power Query screen:

Creating a formula for a custom column in Power Query.

This now creates a Running Total column which calculates the year-to-date sales values:

A Power Query table after adding a Running Total column.

Creating a running total for when criteria is met in Power Query

This time around, I’m going to show you how to create a running total which resets for each salesperson. By doing this, you can track their individual year-to-date totals. If you’re starting from scratch, you will need to repeat the steps from the previous section in order to arrive at your Running Total formula.

Step 1: Convert the Running Total formula into custom function

Once you have created the Running Total formula, you can convert it into a custom function. If you select the query and click on the Advanced Editor button on the home tab, you will see the code for all the steps which were previously created:

Power Query advanced editor showing multiple steps.

To reuse this code and use it as a function, I need to add a place to enter a variable at the beginning.

let runningtotal = (x) =>

In the above example, x is the input. It will refer to the table which the custom function will execute on. And at the end of the custom function, there should be another ‘in’ keyword to circle back to the function, such as follows:

in runningtotal

Here is how my custom function looks like after removing the Changed Type step and setting the source equal to the variable:

The advanced editor showing a formula to calculating a running total.

If you remove any steps, however, just remember to update the formulas. In the “Added Index” step, I updated the step I’m referencing to Source. Otherwise, it would reference a step that doesn’t exist and would result in an error.

After saving, you should notice an fx function next to your query, which you may want to now rename to your desired function name. This is what you will reference it to when invoking the function. In this example, I’m going to call it RunningTotal to keep everything consistent.

Now the function is ready to be used.

Step 2: Group the data

On the Home tab, select the Group By button. This will allow you to break your data into multiple tables.

Selecting the Group By button in Power Query.

In the Group By section, select the option to group by Salesperson and use All Rows as the operation, which ensures no data is lost. I’ll name the new column Data.

Grouping data by salesperson in Power Query.

This now creates a column that contains tables based on the salesperson:

Power Query data which has been grouped by salesperson.

Step 3: Invoke the custom function

On the Add Column tab, press the button to Invoke Custom Function

Invoking a custom function in Power Query.

Then, on the details, enter the column name, the function to invoke, and the data which contains the variable. Since the new ‘Data’ column contains the table, that will be the variable in this example. But if you named the column differently, you would use that name.

The invoke custom function dialog box.

This will now create an additional column, which also contains a table.

A Power Query table after invoking a custom function to create a new column.

If you click on any of the cells in the newly created field, you’ll see that the preview contains the running total field:

A preview in Power Query of a table nested within a field.

Step 4: Expanding the table

The last step involves expanding the tables in the newly created field. By pressing on the two arrows going in opposite directions, you will be able to expand all of the columns.

Expanding a table in Power Query.

You can omit including the Index column as that is not necessary, and the Salesperson field is already there. The only other column you may want to remove in addition to this is the Data column, but that has to be removed with an additional step. Here is how my final table looks:

A Power Query table after adding a field for the running total.

You can see that as the name changes to a different salesperson, the running total doesn’t continue adding; it starts from the first date for that salesperson.

If you liked this post on How to Calculate Running Totals in Power Query, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

Comments are closed.