The CALCULATE() Function – Application

Quick reference

• 00:04 So now that we know what calculate is all about lets look at an example where we can
• 00:08 put it to use.
• 00:09 What I'm gonna do first is I'm gonna actually drill in and
• 00:12 say lets get rid of some of the information here.
• 00:15 So I'm gonna drill specifically just into March and
• 00:18 I'm also gonna go to my PivotTable Tool Design tab.
• 00:21 And I'm going to choose to put grand totals on for columns only,
• 00:25 which will drop all of the total columns on the right hand side.
• 00:29 We're gonna scope this down a little bit, a little bit more deeply here.
• 00:32 So, you'll notice at this point that I've got something for
• 00:36 my March actual budget variance and variance percentage.
• 00:39 And that's good to start with.
• 00:40 What I'd like to do now is I'd actually like to try and
• 00:43 find out what the 2009 actuals were.
• 00:46 And this is gonna be something that I can compare back to from year to year to see
• 00:50 how things are growing in the business.
• 00:53 To do that, I'm gonna go to Power Pivot, we're gonna go to Measures or
• 00:57 Calculated Field.
• 00:58 We'll say New Measure or New Calculated Field of course.
• 01:01 And this guy here, I'm gonna start off, and we're gonna say all right,
• 01:05 let's call this one 2009 Actual.
• 01:09 Now, in order to create this guy,
• 01:10 what we're gonna do is we're gonna start with a Calculate function.
• 01:13 And the fastest way to spell calculate is C+Tab.
• 01:18 It's the most important function there, and
• 01:20 that's why it comes up right under that C key for you.
• 01:22 Naturally, we want to calculate a measure.
• 01:25 I never use a row-column aggregation in this place,
• 01:27 I will always go directly to a measure that I've pre-aggregated.
• 01:30 And that way I can reuse stuff that I already have in place.
• 01:34 So I'm gonna go with actual, so we'll type square bracket A+Tab.
• 01:39 And then, I'm gonna say, all right, what would I like to actually override.
• 01:43 So what I'm gonna do is I'm going to override my calendar.
• 01:47 And I'm gonna choose to use the calendar year,
• 01:50 which happens to be the same field that's on the slicer, right over here.
• 01:55 So go Calendar Year.
• 01:57 And we'll set them =2009.
• 02:00 And then we're gonna close our parenthesis and we will check the formula.
• 02:04 And I can see by looking at my formula, that's not quite so good, so
• 02:07 let's knock one of those zeroes out of there, there we are.
• 02:10 And we'll check again, everything looks good.
• 02:12 And we'll set it to a number.
• 02:14 And we'll drop it down to zero decimal places to be consistent and
• 02:18 use a thousandth separator.
• 02:19 And now what you can see is when we go and say, okay,
• 02:22 it's gonna drop a new value on the table.
• 02:25 It comes to 6884, which is the same as what March 2009 sales are.
• 02:30 But now what I'd like to do is remove the filter from our year.
• 02:37 And notice that even though we get into the 2010 area here,
• 02:42 March's sales of 11,201, we still have a 2009 actual of 6,884.
• 02:48 And this is because even though the year here and in my swell, not in my slicer
• 02:53 here, but the year here on my PivotTable is showing a filter context of 2010,
• 02:58 calculate comes in and it barges way in and says, no, no, no, no, no.
• 03:03 The formula you wrote said it needs to be 2009, so I'm gonna override that and add
• 03:07 it into the, or put it into the filtered context in a slightly different way.
• 03:11 Now, can I use this to a greater advantage?
• 03:14 Well, of course,
• 03:15 I can build a compound measure even though I have these guys in place.
• 03:17 So now I could go back, and I could say all right,
• 03:20 well let's go Measure > New Measure.
• 03:24 Let's make a new measure called Growth vs 2009.
• 03:27 And this one is just simply gonna be a actual minus,
• 03:38 2009 actual.
• 03:42 Because if the actual is bigger than the 2009 actual then we've grown, so
• 03:45 we're good to go there.
• 03:46 So we'll say number, lock this guy down,
• 03:49 use thousands separators, and we'll say OK.
• 03:53 And you'll notice that in the first year, there's no growth versus 2009,
• 03:57 which should probably make sense because that is our base year.
• 04:00 But in the next year, we can see we've gone from up to 11,000 from 6,884, and
• 04:04 that's a positive number, so we've actually grown.
• 04:09 The other thing that's really valuable to know about this kind of stuff is do I need
• 04:13 to keep 2009 actual on my table?
• 04:15 Maybe I don't actually wanna see 2009 repeated every year, maybe I don't care.
• 04:20 So what if I were to go and pull this right off the pivot table and let it go?
• 04:25 Would a growth versus 2009 measure calculate correctly if
• 04:29 this column is not there?
• 04:31 Now, if you think back to the classic Excel ways you'd say no, if you removed
• 04:34 a column, I mean that's gonna cause a ref error, it's gonna blow everything up.
• 04:38 But that's not the way Power Pivot works, remember?
• 04:40 Power Pivot goes back to the raw, unfiltered tables,
• 04:43 applies all the filter context or moves all the irrelevant rows and
• 04:47 then performs that mathematical patterns on whatever is left.
• 04:51 And that include sub measures, those get calculated the same way.
• 04:54 Which means when I let go and remove this, you'll notice that the growth
• 04:59 versus 2009 measure even though it doesn't have the 2009 sales or
• 05:04 2009 actual here, still works quite nicely, which is pretty darn cool.
• 05:08 I can go and I can click and say, give me just 2010 and
• 05:11 you'll notice we've now drilled into a specific year.
• 05:15 We can see what our growth was like, our actuals by the variance.
• 05:18 We're starting to build up a somewhat complicated statement using our own
• 05:22 formula to do it, which is pretty darn amazing and awesome.
• 05:25 And again, I mean, it's gonna slice every single month.
• 05:28 I go into May, and look at that, everything, boy,
• 05:30 May looks terrible compared to what we had before.
• 05:33 But if I go back to April, it's not so bad, and
• 05:35 that's because there's actually only a month of May's data in there anyway.
• 05:39 So when we start playing around, you'll see it's quite dynamic,
• 05:42 everything is working and slicing nicely.
• 05:44 And we don't have to have depressant measures on the pivot table,
• 05:47 in order to actually make use of them.

Lesson notes are only available for subscribers.