Calculated Items

Quick reference

• 00:04 In this video, we're going to look at calculated items.
• 00:07 Now these are very different than calculated fields,
• 00:10 because a calculated field actually aggregates values to return a number.
• 00:15 In a calculated item, it's actually more similar to grouping.
• 00:19 Now what I'm showing you here on the right-hand side is a picture of a pivot
• 00:22 table that's actually using classic grouping,
• 00:24 which we've shown in prior modules.
• 00:26 So you can see we've got three lunch items that are grouped together and four dinner items.
• 00:31 Let's see how this actually is similar to what we have there.
• 00:35 We're going to go click in our pivot table, go to pivot table tools,
• 00:38 analyze fields, items and sets.
• 00:41 Wait, calculated item is grayed out.
• 00:43 Why is that?
• 00:44 It's because you have to have selected a field from one of your row labels
• 00:48 in order to be able to say, let's create a calculated item.
• 00:53 Now when we get in here, we can go in and
• 00:55 let's give our nice little calculated item a name.
• 00:57 We'll go and we'll start grouping here.
• 00:59 We're going to go with breakfast.
• 01:01 And you'll notice it as soon as I do this, it comes back and
• 01:03 the Add button is grayed out.
• 01:05 It won't let me do it.
• 01:06 And the reason being, I can't use the same name of an existing item.
• 01:10 So it's a little frustrating but you can work past that by doing this.
• 01:13 We're going to go with dash breakfast dash.
• 01:17 Now what I'll do is I'll say great, and highlight the zero here,
• 01:20 select breakfast and say, insert item and we'll add that to the pivot table.
• 01:27 Now for whatever reason, it wipes out my Item's list.
• 01:30 But if I go and take a look at some of the other categories here.
• 01:33 If I bring back Category,
• 01:34 it brings me back all of the different items that I actually have here.
• 01:37 So that's nice.
• 01:38 Now I'm going to go through and build out some of my other categories.
• 01:41 Let's make a new one called Lunch.
• 01:45 For lunch, what I'm going to do is I'm going to say
• 01:49 this one's going to be equal to my burgers.
• 01:52 Plus, I'm going to add my sandwiches and my soups and salads.
• 01:59 So grab those three and we'll say Add.
• 02:04 Now why don't we go and make a dinner category and
• 02:08 the dinner category is going to be equal to.
• 02:12 So we'll take all this and wipe it all out.
• 02:14 For dinner, I think we're going to go on, we're going to have, let me see,
• 02:17 what should we throw in here?
• 02:18 Let's grab our appetizers.
• 02:20 We're going to grab our desserts.
• 02:23 We're going to grab our entrees.
• 02:27 And we're also going to grab our food modifiers as well and say Add.
• 02:33 And then finally, we'll make another category here all together for beverage.
• 02:39 So again, we'll hit tab, I'm going to wipe all this one out, go down click Category.
• 02:43 For some reason, doesn't show up right away.
• 02:45 So we click somewhere else and come back to it.
• 02:47 Now we can go and say, this is going to be bottled beer.
• 02:51 Plus, it's going to be canned beer, cooler cider, draft beer.
• 02:55 What else do we need to pick up here?
• 02:57 Liquor, non alcoholic beverage and
• 03:02 wine and we'll say Add.
• 03:06 At this point, we hopefully have everything in there that we need.
• 03:09 If we don't, we can always come back, pick up the category and
• 03:13 modify this piece if we need to do so.
• 03:16 What's going to happen now, I'm going to click OK and you're going to see and
• 03:20 this is where things get a little bit confusing.
• 03:22 We've now just added four new items to our pivot table.
• 03:26 Now, you'll notice that I've actually turned off grand totals for
• 03:29 the pivot table.
• 03:30 The reason being, if I turn them on,
• 03:32 it actually re-summarizes this information and adds it in twice.
• 03:37 So we don't want to do that,
• 03:38 I'm just going to press Ctrl+Z to undo that right now.
• 03:42 Now the key thing is, in order to actually make sure my data isn't duplicated,
• 03:46 I'm going to now need to filter these items.
• 03:49 I'm going to go to label filters and
• 03:50 say, let's go with does not begin with the dash.
• 03:55 At this point, I can now say OK and
• 03:58 that gets rid of those, which wasn't exactly the way that I wanted to go.
• 04:03 Let's go back and try this again and we'll say, begins with the dash.
• 04:08 And this will keep only those items exactly as we actually want to see them.
• 04:12 Now obviously,
• 04:13 this has a lot less data in it than what we see on the right-hand side.
• 04:16 But you can see how you can actually control the grouping levels and
• 04:19 we can actually come up with the same value.
• 04:21 So breakfast, there we are, the 767.
• 04:23 This guys aren't subtotaled, but we certainly could do that very easily.
• 04:29 Here's a report that's done with classic grouping.
• 04:31 This is the way that we would normally do things.
• 04:33 But what you'll see here is we get some funny little categories like
• 04:36 breakfast breakfast, and then we got beverage beverage.
• 04:39 So we've got this extra repeating pieces.
• 04:41 Where a nice little item set can help you out really well
• 04:45 is where you decide to go a little bit more in depth with what you have.
• 04:49 To actually use some more advanced tricks that we'll show you in later modules in
• 04:52 this course.
• 04:53 But this particular pivot table actually uses calculated items
• 04:58 to create a whole bunch of information.
• 05:00 And then uses conditional formatting to format it up to make it look just right.
• 05:04 How does this actually work?
• 05:06 If I go back to pivot table tools analyze, this is a different data set,
• 05:10 which is why you're not going to see these things right off the bat.
• 05:12 Let me grab this, we'll go and grab our calculated items.
• 05:16 You'll see that I've actually got breakfast, lunch, dinner, total food,
• 05:20 all of them starting with a space, so that I can actually highlight these things.
• 05:24 I've got beer as a specific thing that groups three different items for
• 05:27 these guys here.
• 05:29 And I use a mixture of filtering on this, to actually get rid of the items that I
• 05:33 don't want to see, like the other child items that were in this set.
• 05:37 The key here is working with some of the advanced tricks, we'll show you later.
• 05:40 To really take granular control, and
• 05:43 make your pivot table show exactly as you want.

Lesson notes are only available for subscribers.