Showing Different Value Calculations

Contributed by: Dawn Griffiths

Difficulty level: Beginner

Problem

You have a PivotTable and want to display its values as percentages, running totals, and more.

Solution

Follow the steps in Recipe 11.9 to open the Value Field Settings dialog box and select an aggregation; then select the Show Values As tab and choose one of the available options. These options are as follows:

No Calculation

This displays the value of the aggregation without any additional calculations.

% of Grand Total

This displays values as a percentage of their grand total.

% of Column Total and % of Row Total

These display the values in each column or row as a percentage of the column or row total.

% Of

This displays values as a percentage of the value of the Base Item in the Base Field (which you select).

% of Parent Row Total and % of Parent Column Total

These are handy if your PivotTable uses secondary rows or columns because they display the value as a percentage of the parent row or column total. Behind the scenes, they use the calculation (item value)/(parent row or column total).

% of Parent Total

Use this option with secondary rows or columns to display the value as a percentage of the parent item in the Base Field (which you select).

Difference From and % Difference From

These options display the difference and percentage difference between the value and the Base Item in the Base Field (which you select).

Running Total in and % Running Total ...