🥳 GOSKILLS TURNS 10: Get 10 days of free access with code 10YEARS

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

Percent of x Calculations

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 different pivot tables and
  • 00:07 add a variety of business intelligence value to them.
  • 00:10 And we're going to start with this one, our sales by year,
  • 00:13 that's broken down our sales by both years and by alcohol and food.
  • 00:17 And what I'd like to do is I'd like to actually work out what these values are as
  • 00:21 a percentage of various different totals.
  • 00:23 And we'll start with this one.
  • 00:24 What is this?
  • 00:26 250,371 as a percentage of the 1.257 million that we
  • 00:29 actually have here in this particular column.
  • 00:33 So to do that, we're going to go and grab the Amount field,
  • 00:36 which is the same as what we're doing with sales down here,
  • 00:39 I'm going to drag it onto the pivot table a second time.
  • 00:41 Now, this reaggregates to Sum of Amount.
  • 00:44 What I'd like to do here is create a new column for % of Last 5 Yrs.
  • 00:51 So to do that, we'll right-click on it, and we're going to say Show Values As, and
  • 00:56 we'll choose % of Column Total.
  • 01:00 And what you can see here, if you did the math on this,
  • 01:03 is that 250,371 is 19.9% of the 1.257 million.
  • 01:08 And you'll notice that these guys all total up to be 100%, as you would expect.
  • 01:12 The same is true of the actual food sales, as well as the total sales,
  • 01:15 giving us the breakdown of each individual item, which is pretty nice.
  • 01:19 Now, what if I wanted to add something else?
  • 01:21 What if I wanted to see the 250,000 as a percentage of the 616 for
  • 01:25 the total of 2009?
  • 01:28 Well, no problem.
  • 01:29 We'll grab Amount, throw it back onto the pivot table again.
  • 01:33 This is going to be the % of Current Yr.
  • 01:37 And what we'll do is we'll say right-click, Show Values As.
  • 01:41 And instead of choosing % of Column Total we'll choose % of Row Total.
  • 01:47 And what you can see now, if I were to look at 2010s, 243,919,
  • 01:53 that is 39.22% of the 622,000 for the total year.
  • 01:58 The remaining 60.78% is related to $378,000 of food sales.
  • 02:03 So that's pretty cool.
  • 02:04 One more that I want to look at,
  • 02:06 what if I wanted to see my values as a percentage of the grand total?
  • 02:09 So the 3.3 million for all years for all categories?
  • 02:14 Once again, we'll grab the amount, we'll bring this onto the pivot table.
  • 02:18 And once it's here, we'll rename this one to % of Grand Total.
  • 02:24 And for this guy, right-click, Show Values As, and
  • 02:27 this time we'll choose % of Grand Total.
  • 02:30 And just like that, we can see that our sales for 2011 for
  • 02:35 our alcohol is 7.39% of the total sales that are going on in the $3.3 million.
  • 02:41 So this is pretty useful.
  • 02:43 We've got a variety of ways that we can actually go and slice up
  • 02:46 our pivot table and show different aggregations and different percentages.
  • 02:49 Would we use all of these on one pivot table?
  • 02:52 Not likely, we'd probably pick either something that runs vertically or
  • 02:55 something that runs horizontally, not both at the same time.
  • 02:58 But the nice thing is, if we need to, we certainly can.
  • 03:01 Now, let's move across and take a look at sales by category.
  • 03:05 On this pivot table, we're going to do something slightly different.
  • 03:08 We're going to grab a new column and
  • 03:10 add it to the pivot table, just like we've done before.
  • 03:13 So we'll come back and we'll say, hey,
  • 03:14 let's grab Amount and slide it back in here.
  • 03:17 But for this one, we're going to go and say, let's give us the % of Parent Row.
  • 03:23 Now, this is going to be slightly different,
  • 03:26 in that previously we'd use % of Column Totals, we were asking for
  • 03:30 the 21,000 as a percentage of the grand total, 616, for that column.
  • 03:35 I want to see the 21,000 as a percentage of the 250.
  • 03:39 And I want the percentage for
  • 03:41 my 250 here to show as a percentage of 250 versus the 616.
  • 03:46 To do this, we right-click, Show Values As, and choose % of Parent Row.
  • 03:53 So unlike the previous one, where these percentages were all based on the total
  • 03:57 of the entire column, these guys are now saying,
  • 04:00 give me 21,000 as a percentage of 250 and give me 250 as a percentage of the 616.
  • 04:06 So this gives a slightly different way of looking at your data.
  • 04:09 Let's do another one, Amount, we'll grab this one.
  • 04:12 And this one we'll call the % of Parent Column.
  • 04:16 Once again, we'll make a quick change to the aggregation, Show Values As,
  • 04:20 and we'll go with % of Parent Column Total.
  • 04:23 And what you'll see now, if we actually go and start playing around with this one,
  • 04:27 is that, let's move over and take a look at 2013, for example.
  • 04:31 The 32,000 here is 19.83% of the total sales for that particular line of item.
  • 04:38 So that gives you a slightly different way of looking at your data as well.
  • 04:42 Once again, we probably wouldn't use both of these on the same pivot table, but
  • 04:45 it does give us the ability to do so should we need to.
  • 04:48 Now we'll go look at our final pivot table, the Sales Trend.
  • 04:52 Let's take this pivot and once again add two more fields to it.
  • 04:56 We'll drag Amount back in once and twice.
  • 05:00 And this time, I'd like to make two new fields,
  • 05:04 we're going to have a % of 2009 and we're going to have a % of Prior Yr.
  • 05:10 To get the percentage of 2009, we'll right-click, go Show Values As, and
  • 05:15 we'll say % Of...
  • 05:17 This gives us the ability to choose our base field and our base item of 2009.
  • 05:22 And as you'd expect, our 2009 sales are 100% of our 2009 sales.
  • 05:26 But when you look at 2010, it's actually decreased, so
  • 05:30 we're only 97.4% of the 250 that we had in 2009.
  • 05:34 Notice that 2013 is 108%, it's definitely higher.
  • 05:39 Let's do % of Prior Yr as well.
  • 05:41 It starts the same way, % Of..., but we're going to choose the previous item.
  • 05:48 For the very first item, it'll always give us 100%.
  • 05:51 Notice that 2010 is going to look at the previous year,
  • 05:53 just like the previous column did.
  • 05:55 But when we get to 2011, 124%,
  • 05:58 that's because this value is bigger than the 2010 value.
  • 06:02 So once again, different ways to be able to restate the data the way we need.

Lesson notes are only available for subscribers.

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

Facebook 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