Calculated Items

Quick reference

• 00:04 In this video, we're going to look at calculated items, and
• 00:08 calculated items are slightly different than calculated fields.
• 00:11 Basically, what they are is a way that we can actually start grouping in a pivot
• 00:15 table using a different technique than our standard grouping levels.
• 00:19 In a classic grouping scenario, we basically go and we drag our fields and
• 00:23 group them up, so that they'd look like this.
• 00:24 We'd end up with breakfast, and lunch, and dinner items that are all grouped and
• 00:28 organized underneath each other to create a grand total on our pivot table.
• 00:33 In a calculated item scenario, things are a little bit different.
• 00:37 We sort of start the same way as calculated fields.
• 00:39 Although, when we go to pivot table tools analyze, or options in Excel 2010,
• 00:44 you'll notice that when we go to fields item sets, it's actually grayed out.
• 00:47 The reason being is because we have to select a row or column header.
• 00:52 So, if I go ahead and select canned beer, for example,
• 00:54 now you'll notice that because I'm in my row labels, something from this area,
• 00:59 or this area here, I can actually now access my calculated items.
• 01:04 Now, it asks for the same kind of thing, is, would we like to have,
• 01:09 or what would we like for a name.
• 01:11 Well, I'm gonna try and group my items by meal times, so
• 01:14 I'm gonna go with breakfast.
• 01:16 And it says, all right, what categories would you like to be part of that?
• 01:19 I'm gonna say, well, this is great, I'll go select this here.
• 01:21 And I'm going to click Breakfast, and I'm gonna say, OK.
• 01:25 And it says you can't do that because I've already got a field with that name.
• 01:29 Wonderful, all right.
• 01:30 Well, you know what, I can fake this out.
• 01:31 I'm going to go ahead and
• 01:33 put little dashes around it just to make it look a little bit nicer.
• 01:36 So, we've got breakfast with dashes around it, and
• 01:38 that's gonna include the Breakfast category.
• 01:40 We'll add that.
• 01:41 And now, I'm gonna go in and we'll create one for lunch as well, and
• 01:46 instead of using Breakfast, I'm gonna go to Category here.
• 01:50 We'll double click on that to open it up, and replace everything that shows up here.
• 01:54 Inside Lunch, I would like to have, let me see, what would I like to have?
• 01:59 Burgers are part of lunch, and if I go down a little bit here, sandwiches are.
• 02:03 And notice as soon as I hit plus, it unfilters, or takes me back to
• 02:08 the top of this, which is a little bit maddening, but we'll go to Soups/Salads.
• 02:12 So, those three categories will go for Lunch, and let's go for Dinner.
• 02:19 And in Dinner we're gonna go back to Category.
• 02:22 And it just added it to the end, but this formula won't work for us now.
• 02:25 So we've gotta wipe that one out.
• 02:27 And in Dinner, we'll have Appetizers, and we'll have Desserts,
• 02:33 and we'll have Entrees, as well as our Food Modifiers.
• 02:39 So you get the idea fo what we're starting to build here.
• 02:43 I can also, of course, go and pick up Beverage, and double click on Category,
• 02:50 and wipe all of this out, and pick up all the Beverage categories.
• 02:55 So we'll go with bottled beer.
• 02:57 And we'll go with Canned Beer, and Coolers/Cider, and
• 03:02 Draft Beer, and Liquor, and Non-Alcoholic Beverage, and Wine.
• 03:09 And Add.
• 03:10 Now, what happens?
• 03:11 When I add this to my pivot table, you'll notice when I click OK,
• 03:15 it's added all of these items to my pivot table.
• 03:20 You'll notice I've turned the grand total off for this, and here's why.
• 03:23 When I summarize all these values,
• 03:25 you'll notice down on the bottom here I come up to 18, 515,
• 03:29 which happens to be the grand total from the classic view of this pivot table.
• 03:33 This is just a picture, by the way.
• 03:34 So, I've also got the same thing happening for food, 8000,
• 03:38 and this one totals up to 5600.
• 03:40 Oops, hang on, I picked up Wine there.
• 03:43 Let's try here, 5686, which totals up over here.
• 03:46 But here's the problem.
• 03:48 If I went back to my pivot table and
• 03:49 I turned on my Grand Totals, I've now got duplicated numbers.
• 03:55 So in order to work with this, I actually have to go and filter out
• 04:00 all these categories that make up the pieces that I don't want here.
• 04:04 So what I'm gonna do is, I'm gonna say Label Filters,
• 04:07 in this case here, begins with.
• 04:09 And I can just choose to begin it with a dash because all my new categories have
• 04:13 dashes in them.
• 04:15 And that allows me to actually filter the table down
• 04:18 in order to work with the grand total.
• 04:20 Now, I'm gonna show you a couple of interesting pieces about this, and
• 04:23 ramifications.
• 04:25 When I go and use a classic grouping level, and
• 04:28 this is a real pivot table here, you'll notice that if I group my Beverage items
• 04:33 I actually get a subcategory of Beverage, because I have Beverage as a class.
• 04:37 So, I get two levels in here, which kind of looks a little bit funny.
• 04:40 If I use calculated fields,
• 04:42 I can actually rebuild a pivot table to do all kinds of interesting things here.
• 04:46 So all of these totals are based on calculated items,
• 04:53 and then I had to use some conditional formatting tricks to get it to work
• 04:55 correctly for the actual totaling up.
• 04:57 So, if you want really granular control of your pivot table,
• 05:01 you can use calculated items.
• 05:03 But you have to apply a lot of filtering and
• 05:05 grouping to make sure everything gets set up correctly.
• 05:08 Notice that I've got Beer items that are not checked here,
• 05:12 because I've got a beer total.
• 05:13 This is a lot of work.
• 05:14 But with explicit control, we can get it to look exactly the way we want.
• 05:18 Using some advanced tricks, we'll teach you in later modules.

Lesson notes are only available for subscribers.