GoSkills
Help Sign up Share
Back to course

Percent of x Calculations

Compact player layout Large player layout

Locked lesson.

Upgrade

  • Lesson resourcesResources
  • Quick referenceReference
  • Transcript
  • Notes

About this lesson

When you need to display a value as the % of another value on your PivotTable.

Lesson versions

Multiple versions of this lesson are available, choose the appropriate version for you:

2016, 2019/365.

Exercise files

Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.

Percent of x Calculations.xlsx
10.9 MB
Percent of x Calculations - Completed.xlsx
10.9 MB

Quick reference

Topic

Display a value as the % of another value on a PivotTable.

Where/when to use the technique

When you need to display a value as the % of another value on your PivotTable.

Instructions

Accessing % of calculations

  • Drag a value column onto the PivotTable
  • Right click a cell in column you wish to convert to a % calculation
  • Either:
    • Choose Value Field Settings > Show Values As > the desired % of calculation
    • Choose Show Values As > the desired % of calculation

% of Grand Totals

  • % of Column Total:          Expresses value as a % of the Grand Total row for that column
  • % of Row Total:                Expresses value as a % of the Grand Total column for that row
  • % of Grand Total:             Expresses value as a % of the Grand Total of the PivotTable

% of Parent

  • % of Parent Column:        Expresses value as a % of the parent field’s (sub)total for that row
  • % of Parent Row:             Expresses value as a % of the parent field’s (sub)total for that column
  • % of Parent Total:            Expresses value as a % of the (sub)total of the PivotTable

% of… (a specific field)

  • To choose your base field, you need to decide if you’d like the % of to be calculated down a column or across a row
    • Column:               Pick a row field
    • Row:                     Pick a column field
  • % of can be set to:
    • A specific item within a field (useful for comparing growth vs a specific year)
    • The previous item (useful for comparing sales performance vs the prior month)
    • The next item (useful for comparing forecast expectations)
Login to download
  • 00:04 In this video we're going to take three separate pivot tables, and try and
  • 00:07 add a little bit more intelligence to the numbers that are actually showing on them.
  • 00:12 So let's start with the sales by year.
  • 00:15 And what I'd like to know right now is,
  • 00:17 if I can add extra intelligence to the pivot table.
  • 00:19 To find out what our sales are as a percentage of the individual years.
  • 00:24 And also as a percentage of the categories going across.
  • 00:28 The way that I'm gonna do this,
  • 00:29 is I'm going to drag the amount column back on to my pivot table.
  • 00:34 So, sales dollar, is the amount column.
  • 00:36 We're gonna duplicate this one.
  • 00:38 And then what I'm gonna do is.
  • 00:39 I'm gonna right click and go to value field settings.
  • 00:43 And I'm gonna click the show value as tab.
  • 00:46 And this time instead of using no calculation,
  • 00:49 which is what the default is, we're gonna go and choose percent of column total.
  • 00:53 And what we'll do is we'll give this a more
  • 00:57 logical name like % last 5 years.
  • 01:01 So this will show us the sales as a percentage of the last five years of
  • 01:06 sales.
  • 01:07 And we can see that 2009 sales.
  • 01:10 And if I were to go and prove this out, 250,000 divided by the grand total.
  • 01:16 Tells me that it's 19.9% of the grand total of sales.
  • 01:19 This is what this is actually calculating here.
  • 01:21 Is what this is as a percentage of the grand total.
  • 01:27 In addition we've got the same thing replicating for food sales as well as for
  • 01:31 the total sales.
  • 01:31 So that's kinda cool.
  • 01:33 So how about going across the pivot table then.
  • 01:35 If we wanted to have our percentages, so
  • 01:37 that they work as a percentage of total sales.
  • 01:39 Rather than the percentage of the grand total row.
  • 01:41 And that's easy enough to do as well.
  • 01:43 We'll grab our amount, drag it on to the pivot table again.
  • 01:47 We'll right click.
  • 01:48 Go to value field settings, show values as.
  • 01:52 We'll chose percentage of row total.
  • 01:56 And this time around,
  • 01:57 we'll give this one here a name that says percent of current year.
  • 02:01 And, okay.
  • 02:03 And you'll see that now we can see that our alcohol sales were 40% of
  • 02:08 our total sales, and 59.3% of our total sales was our food sales.
  • 02:14 So that works across as well.
  • 02:17 What if we wanted to see, though,
  • 02:19 the percentage as a percent of the grand total for the entire thing.
  • 02:23 Well we can do that too.
  • 02:25 I'll drag Amount onto the pivot table as well.
  • 02:28 Once again, right-click,
  • 02:30 Value Field Settings > Show Values As > % of Grand Total.
  • 02:35 And this will pick up from the very, very bottom corner of all.
  • 02:39 So here we'll say, % of Grand Total.
  • 02:43 And we can now see that of the $3.3 million we've brought in
  • 02:47 over the last five years.
  • 02:49 That our food sales in 2009 were 10.94% of those total numbers.
  • 02:57 And 7.49% of the grand total, was the alcohol sales for 2009.
  • 03:01 So lots of different information that we can get out of that.
  • 03:05 Now let's go look at the sales by category report.
  • 03:09 We've got sales dollars on here, again it comes to the amount column.
  • 03:12 We'll drag amount on here again.
  • 03:14 And this time rather than using percent of the Column.
  • 03:18 Why don't we got and summarize this one by the percent of the parent row.
  • 03:26 And what this is going to do, is it's actually going to create a calculation
  • 03:30 where if we look at the 21,000 divided by the 250,000.
  • 03:37 You'll see that we come back with 8.49%.
  • 03:42 This guy here, though, the 40%, is actually 40% of the 616,000.
  • 03:47 So basically what's happening is this calculation percent of column total.
  • 03:52 Is actually generating or present a parent total rather,
  • 03:56 Is actually generating based on the category sub-heading level.
  • 04:00 And if it is at the top level category, it's generating based on the grand total.
  • 04:04 We can also of course, run the exact same thing by pulling this back onto the table,
  • 04:09 again another sum of amounts.
  • 04:11 And we can right click, value field settings, show values as.
  • 04:16 And this time we can go with the percent of the parent column total.
  • 04:21 This guy, instead of working from top to bottom on the pivot table this way.
  • 04:26 We'll actually look at the items going across the table.
  • 04:30 So, we can see in this particular case.
  • 04:33 Everything is based on 2009 and 2010.
  • 04:37 If we added additional sub-categories to that, say March, April, May, June.
  • 04:41 It would actually be calculating the percentage horizontally across.
  • 04:45 So that is the % of Parent Row.
  • 04:51 And this guy here being the % of Parent Column.
  • 04:56 Last, let's go look at the sales trend.
  • 04:58 On this pivot we're going to throw two more columns on.
  • 05:01 The amount column once, and twice.
  • 05:05 The first one we'll go to value field settings.
  • 05:08 And we're going to do something a little bit different.
  • 05:10 This time we'll choose percentage of and set this to be the percentage of 2009.
  • 05:16 We'll just label it 2009.
  • 05:20 And on the other one, we're going to right click value filled settings again.
  • 05:26 Show values as.
  • 05:27 Choose again, percentage of.
  • 05:29 But we're going to chose the previous year.
  • 05:32 And so again percentage of previous will work.
  • 05:37 Say okay.
  • 05:39 And you can see the differences here that are interesting.
  • 05:41 This will give us the percentage of 2009.
  • 05:43 100% for 2009, obviously.
  • 05:45 97% percent of 2009.
  • 05:47 98.
  • 05:48 You can see that this one actually returns the percentage of the previous year.
  • 05:52 So in the particular case of the 101.24.
  • 05:56 It's actually an increase over 2010.
  • 05:58 Where that's actually a decrease over 2009.
  • 06:01 So another couple of way that you could use percentage of to get some good data.

Lesson notes are only available for subscribers.

Running Totals
05m:10s
Difference From Calculations
04m:37s
Share this lesson and earn rewards

Twitter LinkedIn WhatsApp Email

Gift this course
Give feedback

How is your GoSkills experience?

I need help

Your feedback has been sent

Thank you

Back to the top

© 2023 GoSkills Ltd. Skills for career advancement