How to use the CALCULATE() function in Excel Power Pivot

Excel users have access to a wealth of powerful functions through Power Pivot and DAX, but one function stands out as particularly crucial: CALCULATE(). This function holds the key to dynamically modifying calculations and filtering data in ways that are not possible with traditional PivotTables.

In this post, we will explore the basics of CALCULATE() to create measures that modify one or more criteria regardless of the PivotTable’s overall filter context.

CALCULATE() and the importance of filter context

In traditional PivotTables, every measure is entirely dependent on the overall filter of the PivotTable. To understand the significance of this limitation, consider a scenario where there is a potential issue with our express air freight carrier. We would like to determine the sales revenue attributed to express air for each region while also evaluating the total sales amount.

Using traditional PivotTables we can either display the total sales or the sales filtered for just express air, but not both:

The CALCULATE() function liberates us from the limitation of relying on a single filter for all our PivotTable results. Let’s see how it works, then apply it to this scenario.

How the CALCULATE() function works

For as powerful as the CALCULATE() function is, its syntax is rather simple:

CALCULATE(<expression> [, <filter1> [, <filter2> [, …]]])

The expression parameter represents the calculation or measure you want to evaluate or modify within the context. It can be a simple aggregation function, a complex formula, or a reference to an existing measure.

The filter parameters are optional and can be used to define one or more conditions or filters that modify the evaluation context. These filters can be applied to columns, tables, or other measures.

With this basic structure in mind, let’s apply CALCULATE() to our previously created total sales measure to derive some insights related to the express air portion of sales in the data.

CALCULATE() with one criterion

First, we’ll create a measure called total express air sales which will filter total sales to only include orders where ship mode is equal to Express Air:

CALCULATE([total sales], orders[ship mode] = "Express Air")

Now that the measure has been created, place it in the PivotTable alongside total sales. This allows you to view the total sales and Express Air sales simultaneously in the PivotTable. Remove the filter from the PivotTable — you don’t need it anymore!

CALCULATE() with multiple criteria

AND conditions

Considering our concern for fulfilling high-priority orders, which may be particularly affected by an air freight interruption, we may want to examine sales where order priority is designated “High” along with the ship mode being “Express Air.”

Adding a second, AND condition to a CALCULATE() measure is as simple as adding another filter parameter to the function:

CALCULATE([total sales], orders[order priority] = "High", orders[ship mode] = "Express Air")

OR conditions

When working on conditional logic it’s always a good idea to check for the sensitivity of our results. Even a slight change in the conditions can yield significantly different outcomes. Let’s confirm the sales amount returned when filtering for orders when order priority is “High” or ship mode is “Express Air” ship mode. This will help us understand the impact of these conditions on the results.

To establish an OR condition within the CALCULATE() function, we can utilize two || symbols to connect the two conditions together:

CALCULATE([total sales], orders[order priority] = "High" || orders[ship mode] = "Express Air")

Go ahead and place these various measures together into the PivotTable. Use the Wrap Text feature to make its headers more legible. Now we are able to easily compare sales by a variety of criteria in a single PivotTable. We could even break these results down by another dimension such as customer segment to better understand the potential impacts of an express shipping interruption.

Using CALCULATE() with ALL()

The CALCULATE() function can manipulate filter context in PivotTable measures, but they remain sensitive to overall PivotTable filters. For instance, applying a product category filter affects the total sales measure. Here, only the sales for the Office Supplies category are being totaled, rather than all sales in general:

To view total sales regardless of any filters on the PivotTable, you can use the CALCULATE() function in combination with ALL(). Check out this post to learn more:

Of course, what we’ve done so far in this blog post is already pretty powerful. You can compare your results to the following solutions:

What questions do you have about the CALCULATE() function in Excel Power Pivot or about filter context more generally? Let me know in the comments.

You can learn more about DAX, CALCULATE() and other Power Pivot for Excel topics in my book Modern Data Analytics in Excel: