Contextures

Pivot Tables Show Values As

How to use the pivot table "Show Values As" options, to create custom calculations such as running total or % of column.

Introduction

Use custom calculations (Show Values As) in a pivot table, to compare each amount to other amounts.

For example, show each Customer's total as a percentage of the grand total.

percent of grand total

% Of Grand Total

Use the % of Grand Total custom calculation to compare each value to the overall total.

In this example, the pivot table has Item in the Row area, Region in the Column area, and Units in the Values area. The total will be changed to a custom calculation, to show the percentage for each region's sales of an item, compared to the Sales Grand Total for all Items.

  1. Right-click one of the Units value cells, and click Show Values As
  2. Click % of Grand Total
  3. The field changes, to show the percentage that each item's sales per region contribute to the item's overall sales. For example, about 10% of the overall sales are for Binders in the East region.
  4. % of Grand Total

NOTE: You can change the field heading, e.g. % Sales, to make the data easier to understand. Top

% of Column Total

In this example, the pivot table has Item in the Rows area, Region in the Columns area, and Units in the Values area. Follow these steps, to show the percentage of sales for each item, within each Region column.

  1. Right-click one of the Units value cells, and click Show Values As
  2. Click % of Column Total

    % of Column Total

  3. The field changes, to show the percentage of sales for each item, within each Region column. For example, Pens were 47% of the overall sales in the East region. Top
  4. % of Column Total

% of Row Total

In this example, the pivot table has Item in the Rows area, Region in the Columns area, and Units in the Values area. Follow these steps, to show the percentage of sales for each region, across the each Item row.

  1. Right-click one of the Units value cells, and click Show Values As
  2. Click % of Row Total
  3. The field changes, to show the percentage of sales for each region, across the each Item row. For example, 46% of the Desk sales are in the West region.
  4. % of Row

Note: You can change the field heading, e.g. % Sales, to make the data easier to understand. Top

% Of

The % Of custom calculation lets you compare all amounts to a specific amount. The pivot table has Item in the Row area, Region in the Column area, and Units in the Values area. We'll see two variations on the % Of option.

NOTE: The Base field will always show as 100%, because it is being compared to itself.

% Of Region

In the first example, each region's item sales will be compared to the Central region's sales of the same item, as a percentage.

Follow these steps to change Units to a custom calculation, using the % Of option.

  1. Right-click one of the cells in the Values area, and click Show Values As
  2. Click % Of...
  3. From the Base field list, choose Region
  4. From the Base item list, choose Central
  5. Click the OK button Top

Select Base Item

The pivot table changes, to show each item's sales as percent of the Central region's total for that item. For example, there were 12 desks sold in the Central region, and 25 desks sold in the East region. That's about twice as many desk sales, and the % Of amount shows as 208%.

% of Central Region units

% Of Item

In the second example, each item's sales will be compared to the Binder sales in the same region, as a percentage.

Follow these steps to change Units to a custom calculation, using the % Of option.

  1. Right-click one of the cells in the Values area, and click Show Values As
  2. Click % Of...
  3. From the Base field list, choose Item
  4. From the Base item list, choose Binders
  5. Click the OK button Top

The pivot table changes, to show each item's sales as percent of the Binder sales in that region, or the grand total. For example, in the Central region, there were 12 desks sold and 103 Binders sold. The % Of amount for Central Desk shows as 12%.

% of Binders sold

% of Parent

To show the percent of a subtotal, use the following options (Excel 2010 and later):

For example, what % of binders sold were red? What % of January sales were Pens?

Watch this video to see the steps, and the written instructions are below the video.

% of Parent Row Total

If a parent field is in the Rows area, use the % of Parent Row Total option to show each item's percentage of its parent field's subtotal.

In this example, the pivot table has Item and Colour in the Row area, Month in the Column area, and Units in the Values area. Follow these steps, to show the percentage for each colour's sales, compared to the item's total, in each month.

  1. Right-click one of the Units value cells, and click Show Values As
  2. Click % of Parent Row Total
  3. % of Parent RowTotal

  4. The field changes, to show the percentage for each colour's sales, compared to the item's total, in each month. For example, 195 Binders were sold in January, and 51% (100) of those were Black.go to top

    % of Parent RowTotal calculations

% of Parent Column Total

If a parent field is in the Columns area, use the % of Parent Column Total option to show each item's percentage of its parent field's subtotal.

In this example, the pivot table has Colour in the Row area, Month and Item in the Column area, and Units in the Values area.

Follow these steps to show the percentage for each item's sales, compared to the month's total, for each colour.

  1. Right-click one of the Units value cells, and click Show Values As
  2. Click % of Parent Total
  3. The field changes, to show the percentage for each item's sales, compared to the month's total, for each colour. For example, 40 Brown items were sold in January, and 38% (15) of those were Desks.go to top

% of Parent Column Total calculations

% of Parent Total

The % of Parent Total option allows you to select a base field for the calculations. Then, the percentages are based on the row totals for that field, instead of using each field's immediate parent.

Follow these steps to show the percentage for each item's sales, compared to the month's total, for each colour.

  1. Right-click one of the Units value cells, and click Show Values As
  2. Click % of Parent Total
  3. From the Base field list, choose Item
  4. % of Parent Total calculations

  5. The field changes, to show the percentage of sales, compared to the item's total, for each region and each colour.

For example, 28 Blue Binders were sold in the East region. That is 10% of the total number of Binders sold (289).

The % Parent Row calculation is also shown, to highlight the differences. For the Colour field, instead of showing the percent of the immediate parent (Region), the % of Parent Total column shows the percent of the selected Base Field -- Item. The orange cells in each column add up to 100%.

% of Parent Total calculations

Difference From

Watch this short video to see the steps for creating a "Difference From" custom calculation. Written instructions are below the video.

Difference from

Use the "Difference From" custom calculation to subtract one pivot table value from another, and show the result.

In this example, each region's sales is compared to the previous date's sales. Here is the pivot table showing the total units sold on each date.

weekly units sold

To change the total to a Difference From calculation, follow these steps:

  1. Right-click one of the Units value cells, and click Show Values As
  2. Click Difference From
  3. In the Show Values As dialog box, from the Base field list, choose Date. That field will be used to compare the weekly sales.
  4. From the Base item list, choose (previous). Within the Date field, each week's sales will be subtracted from the previous week's sales.

    select base item previous

  5. Click the OK button, and the pivot table shows the differences in weekly sales. Top

difference from previous date

Difference From Tips

  • To make the data easier to understand, change the heading from "Sum of Units" to "Units Change".
  • Add another copy of the Units field to the pivot table, and show both the total sales and difference in weekly sales
  • Experiment with the pivot table layout, to find an arrangement that is easy to read and understand.

different layout and original values

% Difference from

In this example, the pivot table has Item in the Row area, and Total in the Values area. Date is in the Column area, grouped by Year. There is a pivot table tutorial here for grouping pivot table data.

The total will be changed to a custom calculation, to compare the current year's sales for each Item, to previous year's sales, as a percentage.

  1. Right-click one of the cells in the Values area, and select Field Settings...
  2. In the Field Settings dialog box, type a name for the field, e.g. %Change
  3. Click the Options button, to expand the dialog box
  4. From the Show data as drop down list, select % Difference From
  5. From the Base field list, choose Years
  6. From the Base item list, choose (previous)
  7. Click the OK button

different layout and original values

Running Total in

In this example, the Date is in the Row area, Region is in the Column area, and Units is in the Values area.

For more on Running Totals, see Excel Pivot Table -- Running Totals.

The total will be changed to a custom calculation, to show a running total of units for each region, over the dates.

  1. Right-click one of the cells in the Values area, and click Show Values as
  2. Click Running Total in
  3. From the Base field list, choose Date
  4. Click the OK button go to top

The pivot table now shows a running total for each Region, down through the list of dates.

running total by date

% Running Total In

In Excel 2010 and later versions, you can use the % Running Total calculation, to show the current running total amount, divided by the grand total.

In this example, the Date is in the Row area, Region is in the Column area, and Units is in the Values area.

The total will be changed to a custom calculation, to show a percent running total of units for each region, over the dates.

  1. Right-click one of the cells in the Values area, and click Show Values as
  2. Click % Running Total in
  3. From the Base field list, choose Date
  4. Click the OK button go to top

The pivot table now shows a % running total for each Region, down through the list of dates. This shows that the East region had sold 83% of its total units by 29

percent running total by date

Rank

Use the Rank custom calculation (Excel 2010 and later) to show each number's rank in the set of numbers. The rank can be highest to lowest, or lowest to highest. Watch this short video to see the steps, and the written instructions are below the video.

Use the Rank Custom Calculation

To apply the Rank calculation,

  1. Right-click a cell in the field that you want displayed as Rank
  2. Point to the Show Values As command
  3. Click on one of the Rank options:
    • Rank Smallest to Largest or
    • Rank Largest to Smallest

rank largest to smallest

In the Rank Values window, select a Base field from the drop down list. In this example, the Region field is selected.

rank by region for each product

Then, click OK, to see the ranked numbers. The West region ranked highest in Binder and Desk sales, and also ranked highest in the Grand Total.

rank by region for each product

Index

Use the Index custom calculation to show the relative weight of each cell when compared to its row total, its column total, and the grand total. It uses a special formula to calculate the index values, and is different from the % of Grand Total calculation.

In this example, the pivot table shows the total units sold per region, for each product. The highest amount -- Binders in the West region -- is highlighted.

index for regions and products

To apply the Index Custom Calculation:

  1. Right-click a cell in the Values area, and then click Show Values As.
  2. In the submenu, click Index

Index Custom Calculation

In the next screen shot, the lower pivot table shows the index value for each amount. Lamp sales in the East have the highest index.

index for regions and products

Using the Index custom calculation gives you a picture of each value's importance in its row and column context.

  • If all values in the pivot table were equal, each value would have an index of 1.
  • If an index is less than 1, it's of less importance than other items in its row and column
  • If an index is greater than 1, it's of greater importance than other items in its row and column.

How It Works

Even if two cells have the same value, they may have a different index.

The Index custom calculation formula is:

index formula

For example, in the West region, many more binders were sold than desks, but the index for those products is close.

Because the grand total is higher for the Binders column, the Grand Column Total in the Index formula is larger. The West Binder amount is divided by this larger number, and its resulting index is smaller.

index formula explained

Index Compared to % of Grand Total

The Index Custom Calculation gives different results from the % of Total Custom Calculation, as you can see in the screen shot below. These tables show Auto and Property insurance policy sales, in three regions.

The table at the right shows the % of Grand Total, and the highest value is the same as the highest value in the original pivot table -- Auto policies in the Central region.

% of Grand Total comparison

Remove a Custom Calculation

To remove a custom calculation from a pivot table:

  1. Click the drop down arrow on the Data field button
  2. Remove the check mark from the custom calculation.

Note: If you remove an item from the Values area, it is removed from the pivot table. To replace it, you can drag it back from the pivot table field list.

remove custom calculation

Download the Sample Files

The Custom Calculation (Show Values As) examples shown in this tutorial are based on this zipped sample file.

NOTE: Excel 2010 or later version is required for the Rank options, % of Parent options, and % of Running Total.

Pivot Table Tools

To save time when building, formatting and modifying your pivot tables, use the tools in my Pivot Power Premium add-in. With just a few clicks, you can:

  • copy the formatting from one pivot table, and apply it to another pivot table.
  • change all the values from Count to Sum
  • remove the "Sum of" from all the headings

and much more!

Search Contextures Sites

 

 

 

 

pivot power premium

 

 

power query course

 

pivot power premium

Last updated: October 26, 2017 3:49 PM