Calculated Fields

Quick reference

• 00:04 In this video, we're going to look at a way that we can actually use custom
• 00:08 calculations inside a pivot table.
• 00:11 If you'll notice, I have a list of data here, and I have a couple of different
• 00:14 columns, a sales dollar beverage, and a sales dollar food.
• 00:17 And they're related to the chit numbers that are actually showing up on the left,
• 00:20 the transaction numbers.
• 00:22 Now, I've already made a pivot table that shows the sum of sales beverage and
• 00:26 the sum of food, but I can't sum the total amount of records in this particular
• 00:30 column, because it's not actually in the pivot table.
• 00:34 I can't just drag that on there.
• 00:36 So I'd like to go and
• 00:37 actually create a custom field that will actually allow me to do that.
• 00:40 And the way that I would do that is I'd go select in my pivot table,
• 00:44 go to pivot table tools, and in Excel 2013 it's called analyze,
• 00:49 in Excel 2010 this tab was called options.
• 00:52 And then we'll go to fields items sets, calculated field.
• 00:56 And what you'll see is you get a list of each of the fields that actually show in
• 01:00 my pivot table.
• 01:00 They're all showing here, which is perfect.
• 01:04 What I'd like to do now, is I'd like to create a new measure, or
• 01:07 a new calculated field rather, called Total Sales.
• 01:11 And then I can click in the Formula bar,
• 01:12 and this is really just a double click operation.
• 01:15 We're gonna take Sales Beverage plus Sales Food and then we can say Add.
• 01:21 And you'll notice that it shows up right away.
• 01:24 When I click OK, you'll notice that it actually automatically adds it to
• 01:28 the pivot table sum of total sales.
• 01:31 What's really cool about this is that if you take a look at these fields,
• 01:35 I can actually take sales beverage and
• 01:38 sales food off the table altogether and it still gives me the total sales.
• 01:42 I don't have to have those predecessor columns showing up inside my data.
• 01:47 Now.
• 01:49 What if I'd like to make some calculations, like the average cover, or
• 01:53 the average chit?
• 01:55 I can go back into fields, items, and sets, calculated field,
• 02:00 and I can create a new field called Avg Cover.
• 02:05 And the formula for Avg Cover is going to be Total Sales.
• 02:09 That is the calculator custom column that I created earlier, divided by Covers.
• 02:15 We'll say Add.
• 02:19 And while we're here, why don't we also make another one named Avg Chit,
• 02:22 which will be Total Sales divided by The POSChitNumber.
• 02:28 And add.
• 02:29 There we go.
• 02:32 We can now say OK.
• 02:33 And you can see that one of these worked and one of them didn't.
• 02:38 The average cover is calculated based on total sales divided by the sum of covers.
• 02:43 And that's working quite well and
• 02:44 we can see that it works all the way down the table.
• 02:46 But the average Chit is not working so well at all.
• 02:49 We can see that we have a Count of POSChitNumber.
• 02:52 If I go back to the table, what does count usually indicate?
• 02:55 It indicates that we have text.
• 02:57 So that's not gonna work because in order to be able to actually do any
• 03:01 mathematics it's gotta be based on numeric fields.
• 03:04 So what we'll do, is we'll go and
• 03:06 we'll add a ChitCount field to our table and we're gonna put in =1.
• 03:11 And that way it just sets a value of 1, but
• 03:14 when I hit enter it's a formula so it writes all the way down the table.
• 03:17 I can go back over to Sheet2, right-click, and refresh my data.
• 03:22 And you'll now see that I have ChitCount showing up in my columns.
• 03:26 I've also got a name error that's come up, which is far from inspiring.
• 03:30 So let's go and figure out what's going on with that.
• 03:32 So back into analyzer or options in Excel 2010.
• 03:35 Fields item sets.
• 03:36 We'll go to calculated field.
• 03:38 And let's go take a look at our average cover first, and
• 03:41 figure out what's going on there.
• 03:43 So, for some reason, when we added the new column, it's blown away the logic here and
• 03:48 I'm not 100% sure why that is, but it's certainly easy to fix.
• 03:51 We can just go back and say Total Sales, and
• 03:54 divide that again by the number of Covers, and modify.
• 03:59 And that will actually fix that for us.
• 04:02 We can also go back now and say Avg Chit.
• 04:06 And this time, we'll go in total sales.
• 04:08 Instead of using POSChitNumber, we'll use the ChitCount column that we added and
• 04:13 modify that as well.
• 04:15 And now when we say, okay, they should both calculate correctly.
• 04:20 And this is a beautiful thing for us, we can now actually start taking some of
• 04:23 the fields off the pivot table we don't need.
• 04:25 Maybe we don't need the count of POSChitNumber and
• 04:28 we don't need the sum of covers.
• 04:29 All we're interested in is the total sales, the average cover, and
• 04:33 the average chit.
• 04:34 We could, of course, update our headings appropriately.
• 04:38 What if I decided that I didn't want one of these particular measures?
• 04:42 Maybe I don't want Avg Chit at all after all that hard work.
• 04:45 I can remove it from the pivot table, that's easy.
• 04:48 I just uncheck it or drag it off the field.
• 04:50 But what if I wanted to make sure it was really gone, so
• 04:52 it didn't even show up in the field well anymore at all?
• 04:55 Well again, we go back to Fields Items Sets,
• 04:58 Calculated Field, and what we do is we find it in the list.
• 05:03 Average chit and we click Delete.
• 05:05 And at that point, when we say OK, you'll notice that it completely disappears from
• 05:10 the pivot table all together.
• 05:11 So there you go.
• 05:12 Now, you know how to create, modify, and
• 05:14 remove calculated fields to add a little bit more business logic to your
• 05:18 pivot tables rather than always modifying the underlying data set.

Lesson notes are only available for subscribers.