Difference From Calculations

Quick reference

• 00:04 In this video,
• 00:05 we're going to look at setting up a difference on our pivot table.
• 00:08 And this is a good place where it might actually be really useful for us.
• 00:11 You'll notice we've got categories down the left hand side, we've got our week
• 00:14 numbers across the top, and then the values area for a pivot table.
• 00:17 We're summing up the number of units sold by product by week.
• 00:21 But what if I want to see how that week has changed versus the prior week?
• 00:26 This is where a difference from can be a very useful field on our pivot table.
• 00:30 So to add one of these, what we're going to do is again,
• 00:33 we're going to drag units back into the pivot table again.
• 00:36 This now gives us a Units column and a second column called Sum of Units,
• 00:42 which I'm now going to rename two Diff vs Prior Week.
• 00:47 Now, unfortunately, this didn't really look all that good because we've got text
• 00:50 that's actually hiding under here.
• 00:52 So what I'm going to do is I'm actually going to right click on this entire column
• 00:55 here, a row here, and we're going to go to Format Cells.
• 00:58 And what I'm going to do is go to Alignment, and
• 01:01 I'm going to choose to Wrap Text and say OK.
• 01:03 And that'll actually wrap that in so I can see these things.
• 01:06 Now, of course, this isn't the difference versus the prior week right now,
• 01:09 it's just a regular old sum, so we need to change that.
• 01:13 So I'm going to start again by doing this the hard way,
• 01:15 which is going to Value Field Settings.
• 01:18 When I go into Value Field Settings, I'm still going to use a sum.
• 01:21 I'm happy with the name of my field.
• 01:22 I'm going to go to Show Values As, and rather than do No Calculation,
• 01:26 we're going to scroll down this list until we find Difference From.
• 01:31 Now, when it asks me to set up my difference, it immediately comes back and
• 01:35 gives me the field from the left hand side, which is Category.
• 01:38 And that might be useful maybe, except that in this case I really want to see
• 01:42 the difference versus the prior week which is a column value at the top.
• 01:45 So what I'm going to do is, I'm going to scroll down this list and say, no,
• 01:49 I want to go based on week.
• 01:50 And it says, all right, do you want to base it on?
• 01:53 We're going to choose previous item.
• 01:55 And we'll say OK, and just like that you'll notice that in the first week
• 01:59 there's no difference between that and the previous week because there is no previous
• 02:03 week, so that kind of makes sense.
• 02:05 We've got 66.5 units for appetizers in week number 2,
• 02:09 which is 19.5 units more than what there was in week 1.
• 02:13 And then from 66.5, we went down to 21 so
• 02:16 we've actually got a decrease of 45.5 units.
• 02:20 So it's actually showing us our difference versus the previous weekly value,
• 02:23 which is perfect.
• 02:24 That's exactly what we want.
• 02:26 But what if we also wanted to say, can you show me the difference versus week 1?
• 02:31 So I want to go to a specific week.
• 02:33 And the answer is, of course, we can do that.
• 02:34 We just have to do it a little differently.
• 02:37 We'll grab units, we'll pull it on to the pivot table.
• 02:39 And now, once we got some of the units on the pivot table again, we can go on and
• 02:44 we're going to go and we're going to rename this one to Diff vs Week 1.
• 02:49 Notice again it did not wrap the text.
• 02:51 So once again, we're going to have to go back, right click on the row,
• 02:54 format cells, check the box for wrap text and say, okay.
• 02:57 It's very unfortunate it doesn't stick on our headers.
• 03:00 Regardless, now that we actually have that there, we can go back and make changes.
• 03:05 We could, again, go through Value Field Settings, or
• 03:09 we can go through Show Values As and choose our Difference From.
• 03:14 Within the Difference From,
• 03:15 we can now say instead of using the base field of Category, we'll choose from week.
• 03:19 And what's really nice about this little filter is it actually reduces
• 03:22 it to the fields that are actually on the pivot table, allowing us to choose from
• 03:26 just what's actually relevant, where the Value Field Settings gives us everything.
• 03:29 The base that I'm going to use in this case is not previous,
• 03:33 it's going to be week 1.
• 03:34 And we'll say OK, and
• 03:36 now what you can see again, there's no difference versus week 1,
• 03:39 because that's what we're actually looking at, 19.5, so that totally makes sense.
• 03:44 But when we start moving across the pivot table,
• 03:47 we can see that with 47 units in week 1, we're now down to 21, that is 26 less.
• 03:52 So everything looks like it's working perfectly.
• 03:55 So this is how we can actually set up differences, and
• 03:57 you can obviously set them to work versus the row above,
• 04:00 we've just worked it with columns in this particular case.
• 04:03 And naturally,
• 04:04 if you don't want to see the original units, there's no need to show them.
• 04:07 You can pull it straight off and
• 04:08 just show the differences on the pivot table as well.

Lesson notes are only available for subscribers.