Calculated Fields in PivotTables

Quick reference

• 00:04 In this video, we're gonna continue our journey with PivotTables.
• 00:08 And we're gonna explore what we should do when we want to actually add a calculation
• 00:12 between columns, but for whatever reason,
• 00:15 we're not able to modify the original source data.
• 00:18 We're not able to add a column to it.
• 00:21 So, in this particular case, you can see that we have a Cost, Price, and
• 00:24 Commission.
• 00:25 But let's assume right now that we're not allowed to modify this worksheet in any
• 00:28 way or add any more data to it.
• 00:30 And we'd like to work out what the profit is, or the profit percentage is for
• 00:34 these particular items.
• 00:36 We have a PivotTable that's already created.
• 00:38 It has Price, that's showing on it right now.
• 00:40 And what I'd like to do is I'd like to actually build something that shows me
• 00:43 the profit.
• 00:44 So how can I do that?
• 00:45 Well, what we're gonna do is we're gonna go to PivotTable Tools > Analyze.
• 00:49 And we're gonna go to Fields, Items, & Sets, and
• 00:52 we're gonna make a Calculated Field.
• 00:56 This is the calculated field dialogue, and what it asks us for first is a name.
• 01:00 So we're going to go and give a name of Profit.
• 01:03 When we hit Tab, it comes down, and says, what's the formula?
• 01:06 = 0, so I'm gonna grab my 0 here.
• 01:08 And I'm just gonna delete it, cuz I do want the =.
• 01:11 What I'm going to say is Profit is worth Price, so
• 01:15 I'm gonna double-click on that,- Cost.
• 01:19 -, we'll double-click on Commission.
• 01:22 The reason I double-click these files is if I ever use one,
• 01:25 like Inventory Item, you'll notice that it has some syntax,
• 01:28 where it actually has put quotes around it.
• 01:29 So, rather than type them, I just go and double-click them.
• 01:33 Once we're done with Price- Cost- Commission,
• 01:36 what we can do is we can click Add.
• 01:39 And you'll notice that this adds this to the list of fields right here.
• 01:44 When I click OK, it immediately adds it to the PivotTable.
• 01:48 So it adds it automatically, and it calls it Sum of Profit.
• 01:51 Now of course, I could go back and say, I really don't need that.
• 01:55 So we'll put in Profit space, there we go.
• 01:58 The important thing that I want you to recognize here is that
• 02:00 this actually is the Price, minus the Cost, minus the Commission.
• 02:04 Do you see those fields on the PivotTable?
• 02:05 No, you don't, you don't need them, which is really kind of cool.
• 02:09 So let's go back, and let's add another one.
• 02:10 We'll go back into Fields, Items, & Sets, and we'll go to Calculated Field.
• 02:14 And this time here, we're gonna call this one Profit %.
• 02:20 And for this one, what we'll do is we'll go with Price/ Profit.
• 02:28 And we'll say Add, and we'll drop it onto our PivotTable.
• 02:32 And then we look it and we go, wait a minute, that's not right.
• 02:35 I got that calculation backwards, that's no good at all.
• 02:38 So how do I modify it?
• 02:40 We actually end up going back into Fields, Items, & Sets, back into Calculated Field.
• 02:45 This time, we're going to select our fields from this drop-down list.
• 02:49 And we'll say, okay, it's Profit.
• 02:50 So Profit %, so we'll grab that.
• 02:52 So I didn't mean to go this way around.
• 02:54 What I meant to do was say Profit/ Price.
• 03:02 Now I click Modify.
• 03:04 You'll also notice, if you wanted to just remove your measure altogether,
• 03:07 you can click Delete right here.
• 03:10 Now we'll say OK, and it will modify this to 0.52.
• 03:13 Which of course, I can go to Value Field Settings > Number Format.
• 03:19 We can format this as a percentage.
• 03:21 Maybe we only want 1 decimal place on here.
• 03:24 Say OK, and we'll call this one Profit % with a space after it,
• 03:30 in order to make it show up properly here.
• 03:33 So there we go, we've got our calculated fields on the PivotTable
• 03:37 without even touching our source data.
• 03:39 And this is kind of important, because when we go back and
• 03:41 we look, can we say, yeah, those fields, they don't exist here.
• 03:45 But they certainly do inside our PivotTable.
• 03:49 Sometimes, when you're building calculations,
• 03:51 you're gonna wanna go back and actually build it against the original data set.
• 03:55 Other times, the calculated field will work for you very, very well.
• 03:58 So one thing I would say, though, is always,
• 04:00 when you're building a calculated field,
• 04:02 check the calculation when it lands on the table and make sure that it looks correct.
• 04:06 Because sometimes,
• 04:07 you'll be able to build them, where they look completely out to lunch.
• 04:11 And in those cases, it's better to go back to the source table and
• 04:13 do your calculations there.

Lesson notes are only available for subscribers.