Percent of x Calculations

Quick reference

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