Aggregation

Quick reference

• 00:04 The real benefit of a PivotTable is all about aggregation.
• 00:08 It's about taking a table with a huge number of rows.
• 00:12 Aggregating those massive number of rows down to get a single value that we can
• 00:17 actually have in a cell based on the context of how we've got it sliced up.
• 00:22 But it's not always about sums, although, in most cases,
• 00:25 that's generally what we want, right off the bat.
• 00:28 And this is financial data for sales transactions.
• 00:31 If I were to grab something like Amount and pull it onto the Values area,
• 00:34 you'll notice that it gives me Sum of Amount right away.
• 00:36 Which is pretty much what I'm going to want to have.
• 00:39 But what if I wanted to do something else?
• 00:41 What if I did this, let's grab Units, and
• 00:43 we're going to put that right above Amount here, so we'll put that first.
• 00:47 And notice that, for some reason, this time, I don't get a sum, I get a count.
• 00:52 Well, that doesn't make sense, I want to sum the amount of units that are sold,
• 00:56 not count them, so what is going on here?
• 00:58 And the answer is is that if we actually go back to our Source Data and
• 01:02 we take a look at our Units column.
• 01:05 And we scroll down the Units column for a bit,
• 01:06 you'll find that there's one data point that's slightly different here.
• 01:09 Now, you remember I told you before that it's really important to
• 01:12 have consistent data types down the column?
• 01:15 This one's obviously not, it's text, that's not really cool.
• 01:19 I'm going to fix this, I'm going to type in a 1, and we're going to hit Enter.
• 01:21 And we're going to go back over to our PivotTable and
• 01:24 we're going to take a look at it, and you can see it's still Count of Units.
• 01:28 Now I'm going to right-click and refresh my PivotTable.
• 01:32 It's still Count of Units, what's going on?
• 01:34 Well, the answer is that I've already dragged this field onto the table, and
• 01:38 it's already put in as a count.
• 01:39 So it figures, hey, you know what,
• 01:40 no matter what changes in your data, you're always going to want a count.
• 01:43 Except that I don't, I want a sum, so let's take this off.
• 01:47 We'll take Count of Units off, and
• 01:49 then we'll put Units back onto the PivotTable again.
• 01:52 And you'll notice that now, because the data's been fixed and the Pivot Cache has
• 01:57 been refreshed, I get a Sum of Units just exactly as I wanted, which is good.
• 02:02 Now, I can rename this, we could go and
• 02:03 call this one Units Sold, there we are, that's good.
• 02:08 And I could rename this guy here to call it something like Sales $ as well,
• 02:12 perfect.
• 02:13 But what if I actually did want to get a count of units sold at this point?
• 02:18 If I drag Units back onto the PivotTable again,
• 02:22 it's going to go and sum them again.
• 02:24 It'll allow me to use it twice, but it's going to sum them again, so
• 02:27 how do I actually go about fixing this?
• 02:29 Well, what I'm going to do is right-click on it,
• 02:31 I'm going to show you a couple of ways to do this.
• 02:33 We can go into Value Field Settings, and
• 02:35 what I'm going to do is I'm going to say I would like to see just a count.
• 02:39 So I'm going to name it up the top here, and then I'm going to choose,
• 02:42 down below, Count.
• 02:44 And notice that, immediately, it renames my field.
• 02:46 Because it doesn't trust that I actually renamed at first, so
• 02:48 it says, you want Count of Units.
• 02:49 I'll say, no, that's not really what I wanted, I just wanted it to say Count,
• 02:53 good enough.
• 02:54 And now with this, when I say OK, it's going to flip it back to give me my count
• 02:58 of my units, okay, so there we go, that's all right.
• 03:02 What other aggregations can I do, well, let's grab Amount and
• 03:04 drag this onto the PivotTable as well.
• 03:06 We'll put this all the way down on the bottom here.
• 03:09 And maybe I want to look at something else,
• 03:11 I want to go right-click Value Field Settings.
• 03:13 And maybe this time I want to look at the maximum sale.
• 03:17 So we'll call this Max Sale, and
• 03:19 this should give me the largest value in the dataset.
• 03:22 So if I go and say OK, we can now see the largest value for Bottled Beer was $25.
• 03:27 That's an expensive bottle of beer but, fair enough,
• 03:29 it's still giving me the maximum of all of those records in that particular column.
• 03:34 What if I wanted to look at something else, say,
• 03:36 the standard deviation of sales by category?
• 03:39 Well, what I can do then, I can drag Amount back into the PivotTable as well.
• 03:44 I'm going to put it down here so, once again, we've got it again.
• 03:47 And this time, I'm going to show you a different way to do this.
• 03:49 We can right-click, we could go to Value Field Settings and change things.
• 03:53 But we can also go to Summarize Values By, and
• 03:56 in this area here, we could look at different things.
• 03:58 So maybe I want the average sale, for example, we could go with Average.
• 04:02 There we go, that's the average sales amount, that's all cool.
• 04:05 And again, I could just rename it right in the PivotTable here and
• 04:08 call this one Avg Sale, and there we are.
• 04:13 If I want that standard deviation, it didn't show up there, though.
• 04:16 So let's go back, we'll grab Amount, let's try it again, Summarize Values By.
• 04:22 I'm going to go to More Options, and when I go to More Options,
• 04:25 it brings me back into this area here.
• 04:28 And if I scroll down,
• 04:29 you'll see that I can definitely get the standard deviation as well.
• 04:33 And now when I say OK, we can see that the standard deviation here is
• 04:37 about $4.82 from the mean average of what's actually going on.
• 04:40 So the key thing here is that there's lots of different ways to actually
• 04:44 aggregate your values up and make them show the way that you want.
• 04:47 You can change them by right-clicking and going through Value Field Settings.
• 04:50 Or by going through Summarize Values By and setting them up there as well.

Lesson notes are only available for subscribers.