See all tips | View as pdf

Running Total Table Calculations

You may already know that you can create new fields that aren't part of your data source using custom calculations (as demonstrated in the Conditional Data Labels tip). However, did you know that you can also apply computations just to the values shown in the table rather than the entire data source? These calculations are called table calculations.

Some common table calculations include percent of total, compound annual growth rate (CAGR), and year over year growth. This example shows you how to create a running total table calculation.

Here's how it works.

The text table below shows the Total Sales by Year, Quarter, and Month. Grand Totals are turned on so you can see the Total Sales for each Year.
A view showing the Total Sales by Year, Quarter, and Month.
Now suppose you wanted to see the running total throughout each year. You can use a running total table calculation to compute these values.

To add a running total table calculation:

1. Right-click the Sales Total measure on the text shelf and select Add Table Calculation
Right-click the Sales Total measure on the text shelf and select Add Table Calculation.
2. In the Table Calculation dialog box, select Running Total as the calculation type.
Select Running Total as the type of calculation in the Table Calculation dialog box.
3. Define the calculation so that the values are summarized using Sum and the totals run within the Order Date field restarting at zero for every Year.
Define the calculation so that the values are summarized as Sum, computed across Order Date, and restart at zero every Year.
4. When finished, click OK.
The text table now shows the running totals as you move down each column. The count starts over from zero at the top of each column.
The text table now shows the running totals as you move down each column.
A good way to really see how the totals are computed is to look at both the Sales numbers and the running totals side by side.

To show total sales and the running totals side by side:

1. Click and drag the Sales Total measure from the Data window and drop it on the values in the text table.
Click and drag the Sale Total measure from the Data window to the pane.
2. On the columns shelf, drag the Measure Names field to the right of the Year(Order Date) field. Now the view shows both the Sales Total and Running Totals within each year.
Drag the Measure Names field to the right side of the Year field on the columns shelf.
3. Finally, re-arrange the two measures so that the Sales Total column is on the left of the Running Total column. You can do this by clicking and dragging the headers to the desired position.
Rearrange the columns so that the Sales Total numbers are on the left and the Running Totals are on the right.
The final text table below shows both the Sales Total for each quarter, month, and year as well as the running totals throughout the year from month to month.
Text table showing both the Sales Total for each quarter, month, and year as well as the running totals from month to month.
As mentioned at the beginning, there are many types of table calculations including:
  • difference
  • percent difference
  • percent of total
  • moving average
  • year to date total
  • compound growth rate
  • year over year growth
  • year to date growth
All of these types of calculations can be added using the Add Table Calculation dialog box explained in this example. Although we've used a text table here, these calculations can be applied to any view types and are often very useful for lines and bars.
Note: You can quickly add table calculations to a view using the Quick Table Calculations menu. Simply right-click a measure in the view, select Quick Table Calculation, and select the type of calculation to add.
 

To Learn More

  • Learn more about Table Calculations by searching for Table Calculations in the Tableau Online Help.
  • Contact Tableau Technical Support at: (206) 633 3400 x2
  • Learn this trick and many more at the Tips and Tricks index. Don't forget to bookmark it.