The CALCULATE() Function – Theory

Quick reference

• 00:03 So we've already looked at regular, basic measures.
• 00:07 We've looked at some compound measures.
• 00:09 And the next measure we need to talk about, or
• 00:11 formula specifically, is this magical one called CALCULATE.
• 00:15 So what is CALCULATE anyway?
• 00:18 Well, one of the cool things about a pivot table is it's kinda like dragging a field
• 00:22 into the values area and it creates a whole bunch of SUMIF functions.
• 00:26 Well, CALCULATE essentially is an anything IF function.
• 00:30 It can do SUMIF or COUNTIF or STDDEVIF for
• 00:32 anything you want IF kind of thing to feed into that values area of the pivot table.
• 00:37 The syntax of CALCULATE, it looks like this.
• 00:41 We say CALCULATE, what's the expression we wanna calculate, and
• 00:45 then we optionally add some filters to it.
• 00:47 You can add as many as you want.
• 00:49 That expression Is usually a measure.
• 00:52 So something like this, CALCULATE, instead of measure, you might have actual or
• 00:57 sales dollars.
• 00:58 It's a prerolled measure that you've already explicitly defined.
• 01:01 And then you'll notice that we have a sales year, in this particular case,
• 01:04 equals 2016.
• 01:05 So essentially what's gonna happen here is it will take these sales dollars and
• 01:11 override it to say, I only want the sales dollars if the year equals 2016,
• 01:17 returning the 2016 sales.
• 01:19 You can get into more dynamic comparisons than just equals.
• 01:22 Those sometimes require the FILTER command.
• 01:25 Now FILTER is a lot more complicated.
• 01:27 It's outside the scope of what we're gonna do, but if you are getting into dynamic
• 01:31 comparisons, that's the place that you would wanna go and chase.
• 01:34 Now we've already seen how a regular measure gets calculated.
• 01:38 We know that we start from the raw underlying tables,
• 01:41 we determine the FILTER context, remove the irrelevant rows, and then we go and
• 01:46 we perform the arithmetic on whatever's left.
• 01:48 But what happens when we involve a CALCULATE?
• 01:51 Well, let's take a look at this example.
• 01:53 We've got a little table here and
• 01:55 we have a measure that involves a CALCULATE statement.
• 01:58 You'll notice that 2009 sales is calculating the actual measure, so
• 02:02 that could be a really simple one of sum of transactions actual.
• 02:05 It could be more complicated, it doesn't matter.
• 02:07 It's a measure pattern that's been explicitly defined.
• 02:10 After that, there's some commas, and we can see that we have three different
• 02:13 filters that have been added into the CALCULATE.
• 02:15 We've got Calendar[Year]=2009, we've got COA[Group]="Revenues",
• 02:21 and Dept[Dept Name]="Restaurant").
• 02:23 So if we want to evaluate the $628.68, what do we do?
• 02:28 Well, to be honest with you, it starts as normal.
• 02:32 You go through and you work out your filter context that you can see.
• 02:35 So in this case, that filter context?
• 02:38 The month is January, the account is 61510, and the year is 2010.
• 02:44 That filter context gets applied to the underlying tables, and
• 02:48 in a normal situation it would remove all of the original rows.
• 02:51 You can see that we're down to the year of 2010, the account of 61510,
• 02:54 the month of January.
• 02:56 And we would perform the arithmetic on whatever rows are left over and
• 02:59 return them into our pivot table.
• 03:02 But when you involve CALCULATE, something else happens here.
• 03:05 There's this other step where CALCULATE comes barging in and says,
• 03:08 hang on, we're not done here yet.
• 03:11 We're gonna do some additional things.
• 03:14 If there is a filter that declared in the CALCULATE, and
• 03:18 that already exists in the filter context, and
• 03:21 the case here would be noticing our formula, it says Calendar[Year]=2009.
• 03:26 Well, calendar year is already in the filter context as 2010.
• 03:30 Well, in that case, that filter context is going to get
• 03:33 replaced with what's been declared in the CALCULATE function.
• 03:37 In addition, you'll notice that COA[Group] and Dept[Dept Name],
• 03:41 they're not on that pivot table anywhere.
• 03:43 They're not in a row, they're not in a column, they're not on a filter field.
• 03:47 We don't have a slicer involved in this particular one here, so
• 03:49 those don't exist anywhere.
• 03:51 So those, because they're declared in the CALCULATE,
• 03:54 it's kind of like having a slicer to click on that selection.
• 03:57 They become part of the filter context, they get added.
• 04:00 Once we have that,
• 04:01 we apply the modified filter context to the underlying source tables.
• 04:06 So at this point, Year gets overwritten.
• 04:08 You'll notice that where it was 2010, it's no longer, it's now 2009,
• 04:12 because CALCULATE said it needs to be 2009.
• 04:14 Group and Dept Name, even though they weren't part of the original filter
• 04:18 context, because they're declared in the CALCULATE statement, they get added.
• 04:23 And of course,
• 04:24 we'll filter down all the rows, we'll take a look at whatever's left over, and then
• 04:28 we perform the Measure's arithmetic and return it back into the pivot table cell.
• 04:34 Naturally, if we were to go and evaluate, say the $3,600 there, what will we do?
• 04:39 Well, again, it's a measure, every one's calculated independently.
• 04:42 So we're gonna go through, we're gonna work out the normal filter context.
• 04:45 The year is 2010, month is January, the account is 61520 this time.
• 04:49 Then we'll apply the filters and CALCULATE.
• 04:52 Override the year, add the COA group and the department, department name.
• 04:56 Remove all the irrelevant records, and
• 04:59 then we run the arithmetic pattern on whatever's left.
• 05:02 So that continues on just like a normal measure.
• 05:05 The big difference of CALCULATE, it's the step 1A,
• 05:07 where we come in and we override and change.
• 05:10 So CALCULATE is great for overriding filter context and
• 05:14 adding to filter context.
• 05:16 It can never take away from filter context.
• 05:18 That's the key thing here.
• 05:19 It only adds and modifies things to our filter context

Lesson notes are only available for subscribers.