Total fields are data fields that are either provided in the source data or created by you to summarize the data from other fields in the source data. For example, if the source data has a Sales field that records the amount of each individual sale, a Sum of Sales total field could add up the sales figures for each category in the PivotTable list, or a Count of Sales total field could tally the number of sales for each category. You can also create a total field that uses an expression.
In the field list, the total fields are displayed at the top, under Totals. The Sum of Sales and Count of Units fields are available for inclusion in the PivotTable list as total fields.
In PivotTable lists that are based on source data from OLAP databases, the total field values are precalculated on the database server that supplies the source data. As a result, the commands to add new total fields, move total fields to the detail area, and change the summary function used to calculate the values in a total field are unavailable.
If the total fields available in the field list don't provide the summaries you want, and the source data allows it, you can add new total fields to a PivotTable list. Most often, you will add total fields to the data area. For example, in the following PivotTable list, the Sum of Sales total field has been added to the data area to display summary data for each sport and quarter.
The Sum of Sales total field.
A total field uses a summary function, such as Sum or Count. Depending on the type of source data, you can also use more than one summary function to summarize the data in the same field by adding more total fields, each using a different summary function.
You can also add a calculated total field that uses an expression. For example, to see the year-to-date sales figures, you could create a total field that uses the following expression:
Sum (YTD(), [Measures].[Sales])
The following PivotTable list shows sales and year-to-date sales figures.
Changing the orientation of total fields
You can change the orientation of total fields in the PivotTable list. Displaying the fields in the row orientation lists them down the first column to the right of the row fields. Displaying them in the column orientation lists them across the first row below the column fields.
Showing total values as percentages
You can also show total values as a percentage of a row, column, grand total, or as a total of the item's parent in the row or column axis.
These values will be used if you choose to show total values as a percentage of row total.
These values will be used if you choose to show total values as a percentage of column total.
These values will be used if you choose to show total values as a percentage of the total of the item's parent in the row axis.
This value will be used if you choose to show total values as a percentage of the grand total.
You can display subtotals for any row or column field in the PivotTable list if the list includes at least one total field and more than one row field or more than one column field. If you choose to display subtotals for a field, grand totals are also selected for display. In addition, subtotals and grand totals are displayed for every total field in the PivotTable list where it intersects the row or column field you select.
In the following example, subtotals and grand totals are displayed for both row fields by using the values in the Sum of Sales total field.
Grand total
Subtotal
If you move a field for which subtotals or grand totals are displayed out of the row or column area, the subtotals or grand totals become hidden, but the setting is retained. The subtotals or grand totals are redisplayed if you move the field back to the row or column area.
When you hide subtotals and grand totals for a field, they remain hidden regardless of where you move the field, even if you remove the field from the PivotTable list and later add it back.
Depending on the source data and the type of data you're using in the PivotTable list, you might want subtotals and grand totals to include or omit the values for items of data you've hidden by filtering. You can specify that all subtotals and grand totals either include or exclude any hidden data.