In this tutorial we’ll look at how to create a Power Pivot running total that allows you to see the aggregation at the month, quarter and year levels enabling you to create charts like this:
Note: parts of this tutorial require Power Pivot in Excel or Power BI. Check if your version of Excel has Power Pivot.
Watch the Video
Enter your email address below to download the sample workbook.
PivotTable Running Totals using Show Values As
In both regular and Power Pivot PivotTables we can use the Show Values As tools to create running totals through the right-click menu as shown below:
However, when your data is grouped by dates as mine is, these running totals can only be displayed at one level: either Year, Quarter or Month. For example, if I choose the Year level you can see the data at the quarters level is not a running total from one quarter to the next which makes for a confusing PivotTable:
Instead, what we typically want is this where the quarters increment in line with the calendar year:
PivotTable Running Totals using DAX Measure
In Power Pivot PivotTables and Power BI we can use the DAX formula language to write a measure that calculates the running total as you’d expect.
For this your data needs to be loaded to the Power Pivot Data Model, which you can select when creating the PivotTable:
In my data model I have 3 tables, one for Actual Costs, Budgeted Costs and Forecast Costs and a calendar table to relate the tables together enabling me to create a single PivotTable and chart that contains data from all 3 tables:
Now you can set up the measure via the Power Pivot tab on the ribbon > Measures > New Measure:
I’ll create the measure for the Actual Running Total first, which I’ll call Actual RT. In the Measure dialog box assign the measure to the Actual table (1), enter the name (2), formula (3), and formatting (4):
Build the PivotTable being sure to use the date fields from the Calendar table and the new measure you’ve just written:
Please watch the video for step by step instructions.