Difference From Calculations

Quick reference

• 00:04 In this video,
• 00:05 we're going to look at the methods we can use to show growth in a pivot table.
• 00:10 And the way that we do that is, we pull a value into our values area.
• 00:15 And then we actually go and we set up a difference from calculation in there,
• 00:19 instead of just using a regular sum or an average.
• 00:22 So if we look at the pivot table that I have here.
• 00:25 We can just check the configuration a little bit,
• 00:27 we've got a week across the top.
• 00:28 And you can see the weeks of the month, 1, 2, 3, 4, 5, we've got a category on rows
• 00:33 that's showing us the categories for all of our different sales items.
• 00:36 And in the values area of our pivot table here you can see that we've got units.
• 00:39 And this is just a simple sum of units measures that we've got in here that's
• 00:43 counting up, or summarizing, rather,
• 00:45 all of the individual units that have been sold, the total quantity of them.
• 00:49 Now, what I'd look to do, though,
• 00:50 is I'd like show on here the difference between this week and the previous week.
• 00:56 So the way that I'm gonna do tha,
• 00:57 is I'm gonna drag units again on to the PivotTable.
• 01:00 It's already on there once, but we'll drag it on again.
• 01:03 And you'll see that I now get an extra sum of units.
• 01:06 I'm gonna change the title on this one to growth versus prior week.
• 01:13 And because it didn't wrap very nicely I'm gonna select the row, right click and
• 01:18 format cells, and we'll just go to our alignment and we'll wrap text.
• 01:24 Whoops, that's not where.
• 01:25 Here we go, wrap text.
• 01:27 And we've now got a nice header here, but the numbers are still sum of units and
• 01:31 that's not very good.
• 01:32 So, what we're gonna do is we have two potential ways we could do this.
• 01:36 We could right click on it and we could go to value field settings.
• 01:40 And from value field settings we could go click show values as and
• 01:45 change from no calculation.
• 01:48 Somewhere down here we can see a difference from.
• 01:52 And we can set that up to be the base item of category.
• 01:56 But I would rather actually go and set this up to be based on the week.
• 02:01 And I'm gonna look at the previous week for the base item.
• 02:05 And what's gonna happen is,
• 02:07 in my first week it'll show me that there's no difference from the prior week,
• 02:10 which makes sense because there was no prior week here.
• 02:13 When I look at the next week, this is week two, you can see I had 66 and
• 02:17 a half units here, and it's a difference of 19 and a half units.
• 02:20 In this case we had 10 we had 30 in week two, that was a difference of 20 units.
• 02:27 In week three we had a difference of 13 units again over the prior week.
• 02:30 So that's kind of a neat little way that we can actually
• 02:33 start making up a difference via the previous column.
• 02:38 Now what if I wanted to set up that growth via week 1, so all of the stats,
• 02:42 for all of the pieces versus week number 1, I can easily do that as well.
• 02:47 I'll just grab units and pull that onto the pivot table, yet again.
• 02:52 And instead of sum of units we'll go with growth versus week one.
• 02:57 Now you'll notice that'll update across the pivot table and
• 03:00 again we have to right click and say format cells.
• 03:04 And we'll need to route text on those to make them look a little bit better.
• 03:09 And now we need to modify this measure again.
• 03:11 We do this one slightly differently.
• 03:13 We'll right-click, this time we'll say show values as, different from.
• 03:18 So we don't need to dig into the value fields settings to get there.
• 03:21 We can do it this way as well.
• 03:22 So difference from, when it says what's your base field,
• 03:24 I want the one that's sitting in the My Columns area, which is Week.
• 03:28 And this time instead of previous,
• 03:30 I'm gonna choose a specific value for week one.
• 03:33 Let's say okay.
• 03:35 So you'll notice that again in our first week we
• 03:38 have no difference versus week one.
• 03:40 That makes sense.
• 03:41 In our second week, we have a difference versus week one.
• 03:43 Which is the same as the previous week.
• 03:46 That makes sense again cuz week two, the previous week has the same as what?
• 03:49 Week one is?
• 03:51 But when we get to week three, you'll notice that we have 21 units.
• 03:54 The growth for via the previous week is down 45.5 but
• 03:59 the growth via week one is only down 26 units.
• 04:02 21 off of 47 gives us 26.
• 04:05 So we can see we have a different measure here and
• 04:08 it's working differently for the pivot.
• 04:11 Now, something that's probably worth mentioning, as well,
• 04:13 is that we don't actually have to keep our original units on the table.
• 04:17 We could pull that off and only show the differences if we wanted.
• 04:20 I could even say what's the Growth vs Week 1 if I wanted to pull all of that off.
• 04:25 And show you exactly how much things went up or down, relative to the first week.

Lesson notes are only available for subscribers.