How to build pivot tables in Excel - Chapter 3: Show Values As.. Part 2

The "% of Parent Row Total" function displays each value as a percentage of the parent row's total. 

This is useful for understanding the contribution of each item to the overall total within a specific row.

Let's say we want to see the contribution of each month to the total sales of Product A. 

The parent row for the month row is the total row   

Jan 2024: 1000 / 3900 *100 = 25.64%

The "% of Parent Column Total" function displays each value as a percentage of the parent column's total. 

To see the contribution of sales of Product A to the total sales of January

January 2024: 1000 / 2900 *100 = 34.48%

To understand the contribution of Product A to John's total sales in the month of January

500 / 500 *100 = 100%

The "% of Parent Total" function displays each value as a percentage of a Base Field

When selecting this calculation Excel will ask to select a Base Field

The Base Field is the reference field against which all other fields are measured as a percentage

If we select the Date as the base field, each value will be displayed as a % of the corresponding date

Fred Sales of Product B:

Jan 2024: 700 / 1400 *100 = 50%

Feb 2024: 700 / 1000  *100 = 70%

Mar 2024: Fred has no sales in March 2024

The "Difference From" function shows the difference between each value and another value in the pivot table. This is particularly useful for comparing sales figures between different periods or products. 

Once the "Difference From" Calculation is selected, Excel will ask to select a Base Field and a Base Item 

The Base Field is the reference field against which the values in the PivotTable are deducted from 

The Base Item brings the analysis one step deeper as it identifies which Item within the Base Field represents the value from which all other values will be deducted. It is the specific field within the Base Field that will be used for the calculation.

In case we want to calculate how sales compare to Jan 2024 we select the date as the Base Field and Jan 2024 as the base item 

All items will be shown as the difference from their corresponding value in Jan 2024 

Example: 

Claire sales in Feb 2024: 700-500 = 200

A useful feature available when selecting the Base Item is the possibility of selecting the 'Previous' or 'Next' Item

This feature allows to compare each value to the previous or to the following one.

It is particularly useful when observing the change in values through time 

To compare how the sales of each month relate to the value in previous month, we need to select date as the Base Field and Previous as the Base Item

Example:

Claire sales in Feb 2024 = 700 - 500 = 200

John sales in Mar 2024 = 700 - 500 = 200

The calculation adopts the same logic and parameters of the 'Difference From..' calculation

The only difference is that the result is compared to the Base Item and expressed in percentage terms 

Let's make an example of this calculation when selecting date as Base Field and Previous as the Base Item

Example:

Claire sales in Feb 2024 = 700 - 500 = 200 / 500 = 40%

John sales in Mar 2024 = 700 - 500 = 200 / 500 = 40%

The "Running Total In" function displays the cumulative total of values over a set period or categories. 

This is useful for tracking the progressive total of sales over time. 

When selecting this option Excel will ask to choose a Base Field

Example:

Running Total Sales in Feb 2024: Jan sales 2900 + Feb Sales 3150 = 6050

Similar calculation is performed by the % Running Total 

This calculation shows the running total in % of the gran total 

Example:

Running Total Sales in Feb 2024 = 6050 / grand total of sales 9050 = 6050/9050 = 66.85%

The "Rank Smallest to Largest" function assigns a rank to each value, with the smallest value receiving the rank of 1. This is useful for identifying the lowest sales figures in a dataset. 

When selecting this option Excel will ask to choose a Base Field. 

In thins example the Sales Rep was selected as the Base Field 

Example:

The sales of product A in Feb 2024 showed Claire with 700 and John with 500. John has sold less therefore his sales are ranked 1 and Claire's 2

The "Rank Largest to Smallest" function ranks values in descending order, with the largest value receiving the rank of 1. This helps identify the highest figures