The text table below shows the Sales by Year, Quarter, and Month. Grand Totals are turned on so you can see the Sales for each Year.
Now suppose you wanted to see the running total throughout each year. You can use a running total table calculation to compute these values. Before you create a running total table calculation, first create a preliminary view.
To create the preliminary view:
Step 1
From the Start page, open a new workbook and connect to the Sample - Superstore Sales (Excel) sample.
Step 2
From the Dimensions pane, drag Order Date to the Columns shelf.
Step 3
On the Columns shelf, click the plus sign on the YEAR(Order Date) field to show the QUARTER(Order Date) field.
Step 4
Click and drag the QUARTER(Order Date) field from the Columns shelf to the Rows shelf.
Step 5
On the Rows shelf, click the plus sign on the QUARTER(Order Date) field to show the MONTH(Order Date) field.
Step 6
From the Measures pane, drag Sales onto the Label shelf of the Marks card.
Step 7
Select Table > Column Grand Totals. Your preliminary view should look like this:
To add a running total table calculation:
Step 1
Right-click the Sales measure on the text shelf and select Add Table Calculation.
Step 2
In the Table Calculation dialog box, select Running Total as the calculation type.
Step 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.
Step 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.
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 sales and the running totals side by side:
Step 1
Click and drag the Sales measure from the Data window and drop it on the values in the text table.
Step 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 and Running Totals within each year.
Step 3
Finally, re-arrange the two measures so that the Sales column is on the left of the Running Total column. You can do this by clicking and dragging the headers to the desired position.
The final text table below shows both the Sales for each quarter, month, and year as well as the running totals throughout the year from month to month.
As mentioned earlier, 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.
