Calculated Fields in PivotTables

Quick reference

• 00:03 As you work with PivotTables you'll find that there will come a time where you need to create some
• 00:07 kind of a mathematical calculation inside the PivotTable
• 00:12 that doesn't exist in your underlying data table. So that's what we're going to look at right now
• 00:17 is creating what we call Calculated Fields.
• 00:21 To start we're going to need a PivotTable. So the first thing we're going to do is create that. Click somewhere in our data
• 00:27 go to Insert PivotTable.
• 00:30 Create a new worksheet is fine
• 00:33 and the fields that we're going to add to it are DRG Definition on the Rows
• 00:38 and in the Values we're going to throw in the Total Medical Payments.
• 00:43 And we're also going to throw in the Total Discharges.
• 00:49 All right. Now first thing that we're going to do is we're going to go through and do a real quick format
• 00:53 on this stuff. So right click say Value Field Settings,
• 00:57 Number Formats and change this to Accounting, none, no decimal places.
• 01:04 And it looks like our Total Discharges are already set up that way. That's fine.
• 01:08 I am going to go and change the
• 01:10 Total Medical Payments to Total Payments
• 01:16 and we're just going to call Discharges Discharges.
• 01:21 and I'm going to refresh the Pivot to shrink these columns down.
• 01:24 There we go. Now the goal that we're going to shoot here for here with our Calculated Fields is we're going to try and come up with the
• 01:32 Total Payments divided by the Discharges which will give us our payment per patient.
• 01:37 So we could do this in the underlying data table but we might have to be concerned with how it's going to add up. So in this particular case we're
• 01:44 going to actually force the calculation directly through the PivotTable engine instead.
• 01:49 So to do that we make sure we're selected somewhere inside the PivotTable and we have our PivotTable Tools tab active.
• 01:55 We're going to go to Analyze. On here there is Fields, Items and Sets and in there there's a Calculated Field.
• 02:04 So Calculated Fields basically are a way that we can go through and we can build formulas.
• 02:10 So the first thing it's going to ask us for is the field name so we're going to call this one
• 02:16 Pymnt per Patient
• 02:20 and then it asks us for a formula. So I'm going to highlight the zero.
• 02:24 What I can do is I can scroll down the list and say alright who do I actually have here?
• 02:29 I've got Total Medical Payment so I'm going to double click on that
• 02:34 and then I'm going to divide that by the Total Discharges.
• 02:39 And I'm going to click Add.
• 02:42 And once I do that and I click OK it will immediately add it to the PivotTable for me.
• 02:48 Next we'll just go and reformat this column to make it look a little prettier.
• 02:53 So we'll go into Value Field Settings, choose Number Format.
• 02:58 I always like to use accounting with no symbol and we'll drop the decimals off it as well.
• 03:04 And say OK to that, and OK.
• 03:09 And that looks much better there.
• 03:12 The last thing that I need to do is I need to rename this to something else. I'd like it to say
• 03:16 Pymnt per Patient but it's not going to let me do that if I
• 03:20 try and drop this in here because it did add it to the field well. It is a field name now so when I hit Enter it'll tell me that it already exists.
• 03:27 So I'm going to use my little trick of putting a space after it to just fake
• 03:31 Excel out so Excel thinks it's different but it doesn't look any different
• 03:35 I'll say Enter.
• 03:37 At this point we can refresh our PivotTable and we've now used a Calculated Field to go through and divide the Total Payments by the Total
• 03:44 Discharges to come up with our Payment per Patient
• 03:48 all the way down the table using the same formula for every instance
• 03:52 every row in the entire thing.

Lesson notes are only available for subscribers.