Aggregation

Quick reference

• 00:05 In this video, we're gonna look at the different ways that
• 00:08 a PivotTable can actually aggregate numbers, so it's not all about sums.
• 00:12 We can have counts, we can have a bunch of different things.
• 00:15 I've got a big table of data here, and
• 00:18 I've already created a pivot table ready to go over on this other sheet.
• 00:23 What we're gonna do first is I'm gonna drag a mount onto my values here,
• 00:27 my pivot table, and you'll notice that it immediately comes back and
• 00:30 tells me that it's giving the sum of the amount.
• 00:32 And that's great, and that's typically what we expect when we actually look at
• 00:36 numbers and pivot tables when we're pulling those onto our tables.
• 00:39 Now, I wanna go back and check something out here though, real quick.
• 00:43 Notice the Amount column is all numbers, as is the Units column.
• 00:47 Let's go back and pull units on to the same pivot table, and
• 00:52 what you'll see is that we get something different, we get count of units.
• 00:56 And this is one of the things that drives new people to
• 00:59 pivot tables absolutely crazy.
• 01:00 Why am I getting a count instead of a sum?
• 01:02 It's a numeric column I should be getting a sum.
• 01:04 What's going on?
• 01:06 This is the quickest way to identify that somewhere in that original column of data
• 01:12 you have something that is not numeric, it's being treated as text.
• 01:15 And if we look down here you'll notice that I've pre-filled
• 01:18 this column with an A.
• 01:20 That is one text value in a huge column of numbers but's
• 01:23 enough to throw the pivot table off, okay?
• 01:26 So I'm going to go change this to another value of 1, we'll go back to our table,
• 01:31 and I'm gonna drag Count of Units off, I'm gonna put it back on.
• 01:36 It's still Count of Units, that's kind of weird, wait a minute, let's drag it off,
• 01:41 refresh the pivot cache, and now pull it back on.
• 01:47 Bingo, all right, we get sum of units, that's good.
• 01:50 Now, what if I actually what it to put a count on it instead of a sum?
• 01:54 So let's go and drag, say, for example amount, and
• 01:57 we will drag this back on to the pivot table again.
• 02:01 So we've now got Sum of Amount showing and Sum of Amount2, but
• 02:04 what if I wanted this to be Count of Amount?
• 02:07 I could actually go and right click on this column and say Value Field Settings.
• 02:13 Within here, I could say, you know what, I want to know the Count
• 02:18 of Sales, and now I'll go and choose Count.
• 02:24 And you'll notice that right away, it throws away all of my hard work and
• 02:27 renames it because it doesn't trust me enough to know that I've actually renamed
• 02:30 things first.
• 02:31 So I'll go back and rename this again Count of Sales.
• 02:34 The key is that I've chosen Count down below here, and
• 02:38 you'll notice that when I say OK Both the top here will change,
• 02:43 as will the values that are in this particular column.
• 02:46 So there we go, so we now know that the Count of Sales was 56.
• 02:50 So the actual chips that we use, and the account amount,
• 02:56 or sum of amount rather, of the total sales dollars that we have here, so
• 02:59 we could call this one maybe, Sales Dollars.
• 03:05 We could also go and add even more information to this.
• 03:08 Let's go and drag another amount on here, and
• 03:11 let's change Sum of Amount, in this case Value Field Settings.
• 03:16 Why don't we try and figure out what was the average chit?
• 03:19 The chit, of course, is the bill or the piece that your server hands you at
• 03:24 the end of the day, every one of those particular pieces of paper that has
• 03:26 all of your sales items is the term that I'm using just for reference.
• 03:31 So what is the average?
• 03:32 We'll go with the average sale, and
• 03:38 it looks like that's $7.94 for bottled beer and it's $5.10 for coolers.
• 03:43 Okay, well, that's kinda nice.
• 03:45 What else can we throw in here?
• 03:46 There's all kinds of different fields and formats that we can work with.
• 03:49 We can go back and we could choose, let me see here, how about there's a max,
• 03:54 let me try that, Max of Sales, Max Sale.
• 04:01 Largest sale we had was $25 bottle of beer to one person,
• 04:04 must have been enjoying himself that day.
• 04:06 We could also of course go back and
• 04:08 we could figure out if you're really into your statistics.
• 04:10 We can right click on this guy here and say value field settings and
• 04:14 let's figure out.
• 04:15 Oh, I don't know what do we have?
• 04:16 We've got products, we've got Counts of Numbers, Standard Deviation there we go,
• 04:20 let's grab that guy.
• 04:21 So the standard definition of amount I won't rename that.
• 04:24 There you go, so, on average the standard deviation was about $4.80
• 04:28 depending on the size of the chit that was actually going through there.
• 04:31 So, this is how we go through and we change the aggregation methods that
• 04:36 we're actually using to work with on our pivot table.
• 04:38 You can see we've got lots of options between summing, and counting, and
• 04:41 averages, maxes, and mins, standard deviations,
• 04:43 all kinds of different things that you can actually set up to go.

Lesson notes are only available for subscribers.