Calculated Fields in PivotTables

Quick reference

• 00:04 In continuing your journey with pivot tables, we need to recognize
• 00:08 that pivot tables actually aren't just able to be created against Excel tables.
• 00:12 You can actually create a pivot table directly against a database.
• 00:16 And while I'm not gonna show you in that course,
• 00:18 that does bring up a potential problem.
• 00:20 And that is, if you've connected correctly to a database,
• 00:22 you don't have the ability to add any new columns to your source data because
• 00:27 you're pulling directly from another source.
• 00:29 It's not like pulling it into Excel, where you can write a formula.
• 00:33 So in this case, you'll notice we have a price, cost, and commission but
• 00:36 we don't have a profit field.
• 00:38 So what if I wanted to create one?
• 00:40 Well, if I'm working from an Excel table it's easy.
• 00:42 I write my formula down the side, refresh my pivot table, I can pull it in.
• 00:46 But if I'm connected to another data source, the good news is,
• 00:49 if we actually select our pivot table and go to Pivot Table Tools Analyze,
• 00:54 under Fields, Items, & Sets we have the ability to create calculated fields.
• 00:59 Now, I might wanna create a calculated field, for example, for profit and
• 01:04 that field is going to be, Price -, and
• 01:08 you'll notice I'm double clicking these fields Cost- Commission, why double-click?
• 01:15 Well, because if you get something like inventory item,
• 01:18 is gotta actually do something like wrap it in quotes, so
• 01:20 if you double-click, you will always have the syntax correct.
• 01:23 Once we've done this, it's very important to click Add so
• 01:27 that it actually gets added into the field's list.
• 01:29 We'll click Add, and there it is, there's my profit, and that's good.
• 01:32 If I just clicked OK, that wouldn't necessarily happen.
• 01:35 And now we'll click OK,
• 01:35 and you'll notice that automatically adds to the pivot table.
• 01:40 And here's the really cool thing, price doesn't even need to be here.
• 01:43 Cost certainly isn't here, commission certainly isn't here, and yet
• 01:46 our profit actually shows up.
• 01:48 Now, having a name of sum of profit, that's a little silly.
• 01:51 So let's go in and rename it to profit space in order to get it show correctly.
• 01:56 Now, what if I like to add another field?
• 01:59 Well, I can do that.
• 02:00 Let's go back to Fields Item Sets in Calculated Field.
• 02:03 And what I'd like to do this time is I'll like to make a field for the Profit %,
• 02:09 and for this, I'm gonna take Price and
• 02:13 I'm gonna divide it by the Profit measure that I created earlier, there we are.
• 02:19 So I can actually create a field in here that then feeds something else,
• 02:22 which is pretty neat.
• 02:24 We'll say Add, and we'll say OK.
• 02:28 I think I made that calculation backwards,
• 02:32 there's no way I should be having 193% in my profit.
• 02:37 I'm gonna have to fix this now.
• 02:39 So how do we actually fix this?
• 02:41 Well, we'll go back to Fields, Items and Sets, Calculated Field.
• 02:46 And inside here, now, I really want to actually fix this guy, but
• 02:49 when I click on him, nothing really happens.
• 02:52 So I need to remember that I can actually pull it here from the list, and
• 02:55 here's all of the custom fields that we made.
• 02:57 So here's profit percentage and when I pull that up,
• 03:00 it brings up, the formula that I wrote.
• 03:02 Notice that I have the ability to delete this field right here as well,
• 03:05 if I need to, if I want to clean it up and get rid of it.
• 03:08 But what I'm looking for right now is I'm saying, you know what?
• 03:10 No, this should actually be Profit divided by Price, there we go.
• 03:17 And now we'll click Modify to lock that in, and OK.
• 03:21 And just like that, we're at 52%.
• 03:24 Now of course this isn't great because the name is not good.
• 03:29 So we'll go and we'll make this Profit % space.
• 03:32 And now we can go and change the number format to go and reflect as
• 03:37 a percentage put one decimal place on it and we'll say OK, so there you have it.
• 03:44 The cool thing here is that while I would usually encourage you if you have access
• 03:48 to the Excel table to write your formulas there and then pull them in so
• 03:52 you're not using calculate fields.
• 03:55 If you are connecting to an external data source, you can,
• 03:58 because you cannot manipulate that source data.
• 04:00 You should, however, always check these, because sometimes when you write
• 04:03 a formula here, you get wonky results and you wanna make sure that they're right.
• 04:07 Ideally, do it at the source, but if you can't,
• 04:09 calculated fields are available for you.

Lesson notes are only available for subscribers.