Calculated Fields

Quick reference

• 00:04 In this video we're going to look at how to perform custom calculations inside
• 00:08 a pivot table.
• 00:09 And this is super useful because sometimes you can't manipulate the source data and
• 00:13 you don't have the columns you need.
• 00:15 Other times of course we can.
• 00:16 Before we jump into that, I just want to talk a little bit about the vernacular of
• 00:19 the data set that we're actually using here.
• 00:22 This is a food and beverage data set.
• 00:23 And when we talk about things like chits, what that is is the piece of paper
• 00:27 your server hands you when you actually want to be paying for your meal.
• 00:32 So you could have multiple chits per a table if somebody wanted to separate their
• 00:36 food and alcohol under two separate bills, that would be two chits.
• 00:40 We also have this concept here of covers.
• 00:42 And that's the number of humans that we have sitting at a table.
• 00:45 So one table could have one chit with four covers if there was four people there and
• 00:50 they asked to pay the bill all on one because one person was picking up the tab.
• 00:54 Likewise, there could be four covers and eight chits if everybody said I want my
• 00:58 bill separate and can you split my food and alcohol on a two different bills.
• 01:02 In this particular setup of data,
• 01:03 we also have a units column specific to beverage and food.
• 01:06 And we have sales dollar columns specific to beverage and food as well.
• 01:10 This is kind of interesting because now we can see with our individual chit here
• 01:14 that there was three beverages that were consumed with two plates of food.
• 01:19 Now, let's go and take a look at how this sums up in the pivot table.
• 01:23 So we have our dates, we have a count of how many chits were actually put out,
• 01:26 how many covers were actually there, and the sum of beverage and food.
• 01:30 The problem is, I want to add this together to get the total sale.
• 01:33 And unfortunately in my data set, I don't actually have it.
• 01:36 So what I'm going to do is I'm going to go and
• 01:38 create myself a custom calculation to do this.
• 01:41 And to do that we go to pivot table tools analyze and we'll look for
• 01:45 the button fields items and sets.
• 01:47 And it may be bigger on your screen than mine because my screen is a little bit
• 01:50 compressed right now.
• 01:51 We're going to go and use a calculated field.
• 01:54 Now in the calculated field it brings up the listing of all the different fields
• 01:57 that we have here.
• 01:58 And what I'd like to do is I'd like to make a nice little field here for
• 02:01 total sales dollars.
• 02:04 This is going to be equal to a custom calculation which is going to be my
• 02:07 sales beverage plus my sales of food.
• 02:12 Once I've created it, I'm now going to say Add.
• 02:15 And just like that, it pops into the list down here.
• 02:18 It also will show up in the field list on the right-hand side.
• 02:21 So I'm going to say okay, and just like that, you'll see that it adds it to
• 02:24 the pivot table, and it's adding these two things together, which is fantastic.
• 02:29 What's even better about this, I don't even need to keep my beverage and
• 02:32 food list sitting around here at all.
• 02:34 I can take sum of sales beverage, pull this right off the pivot table.
• 02:38 And my sum of sales food and
• 02:40 even though there's a calculations driving across two individual precedent columns,
• 02:44 they don't need to be in scope to be landed on the pivot table.
• 02:47 So that's pretty darn cool.
• 02:49 Now what if I wanted to do some other things.
• 02:52 What if I wanted to figure out what my average chit was.
• 02:55 How much was actually sold on the average piece of paper that gets handed out, and
• 02:58 what was my average number of covers.
• 03:01 So here's what we're going to do.
• 03:02 We're going to go back up to Fields Item Sets > Calculate field, and
• 03:06 let's figure out if we can make one for Average Chit.
• 03:12 Well, the average chit is going to be based on my total sales dollars.
• 03:16 So the field that I just created previously divided by, and
• 03:20 obviously we've got the counterpoint of ales chits here, so
• 03:22 we'll go with the count of the POS Chit Number.
• 03:26 So there we go, that should give me my average chit.
• 03:27 We'll say OK.
• 03:29 And now I'm going to figure out what is my average cover.
• 03:31 To do that I'm going to make a new column.
• 03:34 We can already verify that my new one just went in here, so that's good.
• 03:37 So now I'm going to overwrite what's showing up here with average cover.
• 03:41 And this one will be total sales divided by covers.
• 03:47 And we can see right here we've got our sum of covers, so
• 03:49 that's what we're looking for.
• 03:50 We'll say Add, and we'll go to say OK.
• 03:55 And what you'll see now is that we end up with one that works and one that doesn't.
• 04:00 We say, well, what's going on?
• 04:02 Well, the sum of covers has information in it that works quite nicely by taking
• 04:07 our total sales and dividing it by whatever's there.
• 04:10 Great, why then doesn't this one?
• 04:13 Because we've got a number over here as well.
• 04:15 Except that when we go back over to this side, we can see that Covers was
• 04:20 a numeric field, and the POSChitNumber is actually a text-based field.
• 04:25 So that's not going to work for us because we can't divide a number by text.
• 04:29 So here's the work around for dealing with this.
• 04:32 I'm going to go and just mark down a nice little field here called Chit, and
• 04:36 I'm going to say equals one.
• 04:38 This formula will now run all the way down the pivot table.
• 04:42 I'm now going to come back over here, right click, and
• 04:45 I'm going to do a little refresh on this to get my new chit field in place.
• 04:50 And I wish I didn't have to do this to be honest with you.
• 04:52 It'll be nice to be able to actually deal with this a little easier.
• 04:55 But we're going to go back into calculated field here.
• 04:58 We now have chit which has a value of one which will be summed up.
• 05:02 What that means is that I can come down and
• 05:04 I can modify my average chit right here.
• 05:07 And instead I'm going to say let's go and find him.
• 05:10 There's average chit.
• 05:12 And instead of dividing it by a POSChipNumber which is actually text,
• 05:17 we'll divide this by the chit column.
• 05:20 We'll click Modify and we click OK.
• 05:22 And at that point, we come back to what we actually need.
• 05:26 So we have our average chit, how much is actually pushed out on each bill?
• 05:29 And what is the average cover?
• 05:31 What's the average human paying?
• 05:33 Now what if I decide I didn't want this in my pivot table at all?
• 05:36 Maybe I say hey, I don't have it, forget it.
• 05:38 I want to get rid of it.
• 05:39 Well, obviously I can come back and say let's just pull it off the pivot table.
• 05:43 But the thing is it still lives field list.
• 05:46 And if I don't want to surface that someone, I might want to get rid of it.
• 05:50 How?
• 05:50 Well, we go back to calculate fields.
• 05:54 We find the average chit right here.
• 05:58 We'll pull it out from our list, it's the proper place to go.
• 06:00 And then we'll say Delete, and we'll say OK.
• 06:04 And now you'll see it disappears from my field list on the right hand side as well,
• 06:08 and it's not listed in my table anymore.
• 06:11 So calculated fields are super useful if you can't manipulate your data set if you
• 06:15 want to do calculations on the fly.
• 06:17 Oftentimes though, I'd prefer to go back to the original data set and
• 06:19 make things easier by putting the source data in there.
• 06:22 Because sometimes it's easier to just build things that way.

Lesson notes are only available for subscribers.