Percent of x Calculations

Quick reference

• 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.