Percentage Difference From Calculations

Quick reference

• 00:04 In addition to a straight out difference from calculation for
• 00:08 a pivot table we also have the ability to get a percent difference from as well.
• 00:12 This one can look a little bit strange depending on how you do it, so
• 00:15 let's go and explore this one and see what we come up with.
• 00:19 What I'm going to start by doing is going and grabbing the Units column and
• 00:22 pulling it onto my pivot table again.
• 00:24 So I now have my Units and I've got my Sum of Units.
• 00:27 And I'm going to change the aggregation on this to Show Values As and
• 00:31 we'll use a % Difference From.
• 00:33 Now, at this point, it's going to give me a horizontal one.
• 00:36 I'm going to say, well, rather than Bottled Beer, let's go with (previous),
• 00:39 because there's some strange stuff that happens when you do this.
• 00:43 When you click OK,
• 00:43 you'll notice that not all of the fields that are actually here get filled in.
• 00:47 Well, why is that?
• 00:49 Well, if you look at Bottled Beer, there's no previous unit.
• 00:52 So it's not going to give you any value for
• 00:54 this because there can't be a percentage difference from nothing.
• 00:57 When we get to 4 units for Canned Beer, you can see that we've actually got
• 01:00 a difference of 6 units so it's dropped 60% over the 10.
• 01:04 Then we get Coolers Ciders and we're blank again, we have no units.
• 01:07 So when we get to Draft Beer because there is nothing there it can give as a decrease
• 01:12 or an increase per se that works out to a meaningful percentage, so
• 01:15 it leaves it blank.
• 01:17 But then back at Liquor, well we now know that we've actually dropped by 29 units so
• 01:22 now we can actually make a proper calculation out of it.
• 01:25 So it looks a little bit strange when you do this.
• 01:27 I want to show you how to reconfigure one of these when you realize that,
• 01:30 hey, I didn't really want this going up or down.
• 01:32 I really want it going left, right.
• 01:34 I'm going to go back and I'm going to reapply the % Difference From.
• 01:39 And it says okay, you didn't want Category, what do you want instead?
• 01:42 I'm going to say hey, you know what, I really wanted Week.
• 01:45 So let's get the percentage difference from the previous week.
• 01:48 And when I say OK, it now flips it so
• 01:52 I can say that's cool, % Difference From Prior Week.
• 01:58 And of course, this doesn't look very good on my header, so I'll right click,
• 02:02 format cells, wrap the text and there we go, now we can see what's going on.
• 02:07 Now this looks a little bit strange because we've gone from ten units
• 02:10 to 30 units, which is a 200% increase.
• 02:12 You think, well, wait a minute, is that 300%?
• 02:14 It's actually not because we're actually using the difference from the prior week.
• 02:18 The difference from the prior week is actually 20 units, and
• 02:22 therefore it's a 200% increase over the original 10.
• 02:26 So it looks a little bit strange potentially to the way this would work.
• 02:29 And then when we move across,
• 02:30 we now have 13 units difference between these two guys here.
• 02:34 So what that means is 13 as a percentage of 30 works out to 43%, and there we are.
• 02:38 We have this guy all set up.
• 02:40 One more, let's go grab units, we'll pull this onto the pivot table again.
• 02:45 And for this one, what we're going to do is we're going to make something that is
• 02:48 a % Difference From Week 1.
• 02:53 Again, the formatting is awesome.
• 02:55 Right click, Format Cells, check wrap text.
• 02:59 That looks a little bit better, and now we'll make the change to our aggregation.
• 03:04 Show Values As, % Difference From.
• 03:07 We'll go and change it to Week for our column header, and
• 03:11 we'll say let's lock it in to Week 1.
• 03:13 Once again, this area will all blank out because there's no difference from Week 1
• 03:17 to Week 1.
• 03:18 But when we get to Week 2, you can see that the percentage difference is exactly
• 03:23 the same as what we had when we had prior week.
• 03:26 Well, that makes sense because Week 1 is the prior week.
• 03:29 But when we move over a column, you'll now notice that there is a big difference.
• 03:34 When we get into Week 3, the percentage different from the previous week,
• 03:38 which is Week 2, is 43.33%.
• 03:40 But the percentage difference from Week 1, which where we had 10 units,
• 03:45 we're now at 43 units.
• 03:46 So the difference is 33 units, which is 330% of 10 units, okay?
• 03:52 So that's the way this thing is working in order to calculate these things out.
• 03:55 So whether or not this is something you use frequently is up for
• 03:59 debate of course, but it's nice to know that we have the option.

Lesson notes are only available for subscribers.