Wednesday, December 6, 2017

Calculate a percentage for subtotals in a PivotTable

Calculate a percentage for subtotals in a PivotTable

To show percentages such as % of Parent Total, % of Grand Total or % Running Total In in a PivotTable, choose from the Show Values As options.

To show calculations side by side with the values they're based on (for example, to show the % of Grand Total next to the subtotal), first you need to duplicate the value field by adding it more than once.

  1. In the Field List, drag the field you want to duplicate to the Values area, and place it right below the same field.

    Excel adds the value field to the PivotTable with a unique identification number appended to its name. Change the name if you want.

  2. Right-click the value field in the PivotTable, and then pick Show Values As.

  3. Click the option you want.

Value field calculation options

Use this c alculation option :

To show or calculate:

No Calculation

The value that is entered in the field.

% of Grand Total

Values as a percentage of the grand total of all the values or data points in the report.

% of Column Total

All the values in each column or series as a percentage of the total for the column or series.

% of Row Total

The value in each row or category as a percentage of the total for the row or category.

% Of

Values as a percentage of the value of the selected Base item in its Base field.

% of Parent Row Total

Values as: (value for the item) / (value for the parent item on rows)

% of Parent Column Total

Values as: (value for the item) / (value for the parent item on columns)

% of Parent Total

Values as: (value for the item) / (value for the parent item of the selected Base field)

Difference From

Values as the difference from the value of the selected Base item in its Base field.

% Difference From

Values as the percentage difference from the value of the selected Base item in its Base field.

Running Total in

The value for successive items in the chosen Base field as a running total.

% Running Total in

The value as a percentage for successive items in the chosen Base field that are displayed as a running total.

Rank Smallest to Largest

The rank of selected values in a specific field, listing the smallest item in the field as 1, and each larger value with a higher rank value.

Rank Largest to Smallest

The rank of selected values in a specific field, listing the largest item in the field as 1, and each smaller value with a higher rank value.

Index

Values as: ((value in cell) x (Overall Grand Total)) / ((Grand Row Total) x (Grand Column Total))

More about PivotTables

No comments:

Post a Comment