Pivot Table – Show Values As – Part 2

image We continue to explore Show Values As options in Pivot Tables.We will see the practical application of these options as well.Estimated reading time 10 min

Please read the previous article first. This is a continuation of the series.
Adding formulas OUTSIDE Pivot Tables = Inefficiency

Download Sample File

Download this file and follow along…

Create a Pivot Table from the data. Drag Amount in Data area and Segment in Row area. Right click in Amount (data area) and choose Show Values as – % of column total.

Percentage of Parent Row Total

This option is a bit tricky and difficult to understand. So let us start from the previous example. One Column shown as percentage.

Now the interpretation is easy. Value for each segment is the contribution to the total (which is 100%).

image

But now let us add Customer Type to the row area – below the Segment. Now try to interpret each value…

The values for Segment contribution are now subtotals (in Bold). But look at the value for Preferred and Non-Preferred customers. Those are also percentages.

Percentage of what? Grand Total. So it is very simple.

image

The Preferred and Non-Preferred values of each Segment sum up to give you the Segment total.

Can I compare a customer type value across segments?

For example, Exports Preferred is 3.22% and Government Preferred is 2.97%

Both values are contributing to the GRAND TOTAL. So in that sense they are comparable. But within the Preferred segment they are not comparable.

If such comparison is to be done, we need the Preferred and Non-Preferred values to total to 100.

The first field is the Segment – Customer Type is under it. Therefore, the Segment field is called the Parent Field and Customer Type as the Child Field.

Now right click in the data area and choose % of Parent Row Total and see what happens.

image

Now we can compare Exports – Preferred contribution (32.45%) to
Government – Preferred contribution (35.52%)

All pairs of Preferred and Non-Preferred now total to 100.

Brilliant feature – but almost completely unnoticed. Some of you may have manually added these formulas to achieve this result!

If the same situation happened while these two fields were in columns, you should use the % of Parent Column Total option.

image

This option requires too much space horizontally. Therefore, Parent Row Total is visually easier to interpret.

What Next

Till now everything was being compared to the Grand Total. In the next article we will understand how to change the benchmark of comparison.

Articles in this series

Adding formulas OUTSIDE Pivot Tables = Inefficiency
Pivot Table – Show Values As – Part 2 (this article)
Pivot : Show Values As – Part 3
Pivot: Running Totals and Ranking
Adding calculations WITHIN Pivot Tables
The amazing Calculated Items in Pivot Tables

***

7 thoughts on “Pivot Table – Show Values As – Part 2

  1. Pingback: Adding formulas OUTSIDE Pivot Tables = Inefficiency | Efficiency 365

  2. Pingback: Pivot : Show Values As – Part 3 | Efficiency 365

  3. Pingback: Pivot: Running Totals and Ranking | Efficiency 365

  4. Pingback: Adding calculations WITHIN the Pivot Tables | Efficiency 365

  5. Pingback: The amazing Calculated Items in Pivot Tables | Efficiency 365

  6. Pingback: Knowledge Pack: Data Analytics | Efficiency 365

  7. Pingback: Act Now: Discover one new and useful thing from familiar data | Efficiency 365

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s