Power Pivot's essential CALCULATE: How to Catch Only The Data You Want

Note: This is a function that only works with Power Pivot. Check if your Excel has Power Pivot, and set it up with these instructions.

Let’s talk about CALCULATE(). What exactly is CALCULATE()? Why is it important for you to know about it? What does it allow you to do?

Oh boy, we could go on forever. This function will turn you into “Bob the Builder” of Excel if you master it. But if we had to explain its awesomeness in simple terms:

Imagine having a fishing net that knows exactly what type of seafood you want to catch, right down to the exact type, size, colour etc. of fish (if fishes are what you are looking to catch for the day). No need to deal with by-catch that you have absolutely no interest in. CALCULATE() allows you to do exactly that with your data.   

How does it work?

Understanding the CALCULATE() function is actually pretty intuitive and best explained with a simple example.

Let’s keep with the fishing theme. Say you are a fishmonger and you have been offered what you think it is a good deal for an extra batch of grouper for the next 12 months. Before you decide to commit, you want to check how your grouper sales stacked up against your normal monthly sales. So you pull up your past sales data and import it into Power Pivot’s data model. Oh and you rename the sheet in the data model “Seafood sales data” (this is somewhat important, you will see why later).

How will the CALCULATE() function help you with this task?

Step 1. You create a measure that will help summarise the Sales figures. You will use the SUM() function for this measure. You name this measure [Total Sales].

Step 2. You create another measure that narrows down the summarised Sales figures to reflect only grouper sales. Here is where you will use the CALCULATE() function.

Step 3. You create a pivot table and put both measures side-by-side.

Going into the details

Measures - what are they all about

If you missed our previous blog post about measures and have never used Power Pivot before, you are probably quite confused now. You would most likely be asking yourself “what is a measure to begin with?”.  

Here’s an introduction to measures. If you feel like dealing only with a quick summary at the moment, a measure is basically a set of summarised values that has been created by a function or a combination of functions. Examples include Year-To-Date (“YTD”) sales or last 12 month sales etc.

So in Step 1 above, we have essentially created in just one step the Total Sales for all the different attributes (i.e. total sales for each year, month and type of seafood).

Oh, remember we mentioned earlier that your data sheet was named “Seafood sales data”? Here’s why it is an important detail. When writing a measure, both the sheet name and the column that the data is sitting under are referenced. So be sure to give an easy-to-reference name to your sheets.

CALCULATE() - Putting it into action

Now that you know what a measure is, it makes explaining the CALCULATE() function a lot easier. The function reads: =CALCULATE(Expression, [Filter 1], [Filter 2],...). Don’t be intimidated by the word “expression”. “Expression” in this case basically means measures (or if you don’t have an existing measure, you should write a function in the same format as a measure).

So you put the Total Sales measure you created in Step 1 into the function and you have =CALCULATE([Total Sales], [Filter 1], [Filter 2],...). The [Filter x] fields are for you to specify which attribute you want to focus on and which details of that attribute you want to highlight.

To get the grouper sales figure, we will need to define one filter argument. The complete function will read as =CALCULATE([Total Sales], ‘Seafood data sales’[Types]=”Grouper”).  

Pretty intuitive once you understand what the fields mean, no?

Shine bright like a diamond

What makes CALCULATE() truly shine though is when its filter arguments are defined by functions instead of static data. It brings such great simplicity to formula crafting. If you have ever had to create a formula that has functions nested within functions nested within functions to help dynamically filter your data, you will understand how this can all very quickly snowball and get out of hand.

We go back to you being a fishmonger to help illustrate this point. A couple of months have passed since you committed to selling more grouper and you are curious about how your overall sales (i.e. YTD sales) are doing.

Step 1. For CALCULATE() to help you with this, it is imperative that you first create a separate date table and then link it to the Seafood sales data table. Why? Fundamentally, the separate date table allows time-related functions to run uninterfered by pivot table filters that you may want to apply to your Seafood sales data table. There are other amazing benefits to reap from having a separate date table in place but that’s for another time in another article.   

 Relate tables together by linking them in Power Pivot’s Data Model diagram view

 Step 2. You can then easily apply the following measure: =CALCULATE([Total Sales], DATESYTD(‘Date Table’[Dates])).

 Power Pivot write measure with DAX using CALCULATE and DATESYTD

 Want to compare YTD sales figures to the same period last year? Just tweak the measure to read as: =CALCULATE([Total Sales],(DATESYTD(SAMEPERIODLASTYEAR(‘Date Table’[Dates]))).

 

Is that game-changing ease or what? We’re not saying that your formulas will cease to be complex ever again but for those that can be made simple, Power Pivot has done it with functions like CALCULATE().

Register for our training course and be wowed by other Power Pivot functions that will make your Excel life so much easier. What’s more, if there’s a set of data that you think will benefit from Power Pivot functions but it has somewhat stumped you, bring it for the 90-minute consultation session that’s complimentary with your sign-up and we can crack our brains together to find a solution!