Working with a Pivot Table, we know that each row (or column) in a Pivot Table report act as a filter for the entire data set.

If we have a table of daily sales and we create a Pivot Table report showing total sales by month, the Pivot Table will take each month in the report and apply it as a filter to the entire dataset.

The result of each filter is a reduced version of the whole.  These remaining rows in the table are then aggregated (in this example, summed) to produce a single value result, i.e., the total sales for that month.

The filter/aggregate process is repeated for each month in the Pivot Table.

This process would be the same if we had 100 product names or 20 salesperson’s names in the Pivot Table’s rows.

The DAX CALCULATE function is like a Pivot Table that uses Pivot Tables as its source.

If that last statement made you go, “huh?”, then let me clarify.

Instead of taking a table of transactions and applying filters and aggregations to it, the CALCULATE function takes a Pivot Table as its source and applies additional filters and aggregations.

Think of it as refining the original Pivot Table or replacing certain aspects of the original Pivot Table with different aspects to tell the story a different way.

## The DAX CALCULATE Function

The CALCULATE function is the only DAX function that can completely change the filters coming from the Pivot Table.

Calculate can overwrite all existing filters or supplement the existing filters with other filters.

The syntax for CALCULATE is:

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

The <expression> portion of CALCULATE can reference an existing Measure where the expression already exists, or the expression can be placed directly within the CALCULATE function.

``CALCULATE(SUMX(Sales, Sales[Quantity] * Sales[SalesPrice] ), filters go here)``

…or…

``CALCULATE([Total Sales], filters go here)``

…assuming we create a Measure called [Total Sales] that contains the SUMX expression.

The filter options are defined as a traditional Boolean filter, like “Employee[Salary] > 50000”.

``CALCULATE([Total Sales], Employee[Salary] > 50000)``

Featured Course

## Power Excel Bundle

10x your productivity in Excel 💪. This bundle includes Master Excel Power Query course & Master Power Pivot and DAX course. It’s Excel’s Ultimate Power Tools in ONE convenient (cost savings) bundle. Learnings apply to Power BI as well.

## An Example of CALCULATE in Action

We start with a Pivot Table that shows “Total Sales” for the “Year” 2021 by “Month”.

We could write a DAX Measure that uses CALCULATE to reduce the existing “Total Sales by month for 2021” by filtering each row in the Pivot Table to only rows in the data where the product type is classified as “Innovative”.

Or where the “Innovative” products were purchased in bulk (defined as a purchase of 10 or more at a time.)

Each of these Measures that use CALCULATE is reducing the original Pivot Table aggregations to a lower level of granularity, just like a Pivot Table does to the original data set.

The Measures respect the original Pivot Table filters (year and month), then apply additional filters.

## CALCULATE’s Processing Order

An important concept behind the CALCULATE function is that the expression is always the last portion to be evaluated.

The order of processing for CALCULATE is as follows:

1. The visual’s filters are applied. These can come from a Pivot Table’s rows/columns/filters or other visualization, like a Slicer.
3. If a filter used by CALCULATE already exists in the visual, the visual’s filter is replaced by CALCULATE’s filter.
4. Lastly, the expression is calculated.

Understand this: if there is a conflict between similar filter logic of the visual and CALCULATE, the filters from CALCULATE “win”.

The expression is calculated after all filters have been negotiated and applied.

Featured Course

## Master Excel Power Pivot & DAX (Beginner to Pro)

Transforming data into meaningful reports can feel like being in an Olympic event. It is hard! Join the course and learn how to use Excel’s Power Pivot and Data Modelling tools to make reporting easy and fast.

## Breaking Down an Example

We’ll start with a Data Model that has several tables connected via relationships.

We will utilize the following table and fields in our example:

• Sales[Quantity]
• Sales[SalesPrice]
• tblProduct[Type]

The tblProduct[Type] field contains two classifications for products: “Innovative” and “Standard”.

### Creating a Measure to Calculate “Total Sales”

In each instance of the DAX CALCULATE function, we want to multiply the [Quantity] by the [SalesPrice].

We can make our lives easier if we create a Measure that performs this calculation, then we can use this calculation as many times as we like without having to “reinvent the wheel” so to speak.

In our Data Model, we create a Measure called [Total Sales] that contains the following formula.

``=SUMX(Sales, Sales[Quantity] * Sales[SalesPrice] )``

This will shorten our CALCULATE functions greatly by not having to write the lengthy SUMX function each time.

### Creating a Measure to Calculate “Total Sales for Innovative”

Now that we have our generic [Total Sales] Measure, we can write a Measure using CALCULATE to reduce existing results further by only examining those records where [Type] is equal to “Innovative”.

In our Data Model, we create a Measure called [Sales Innovative Products] that contains the following formula.

``=CALCULATE( [Total Sales], tblProduct[Type] = "Innovative" )``

Adding this new Measure to our existing Pivot Table produces the following results.

If you’re having difficulty visualizing how these new values have been derived, check out the following Pivot Table.

In the above Pivot Table, we have added the [Type] field to the Rows section to break down each month by either “Innovative” or “Standard”.

Next, we filter the [Type] column to only include “Innovative” entries.

When using the DAX CALCULATE function, these new calculations are produced in the background using the same process.  The results are the only part of the process we can see.

### Creating a Measure to Calculate “Total Sales for Innovative Products Sold in Bulk”

Now we will write a Measure using the DAX CALCULATE function to reduce existing results further by only examining those records where [Quantity] is greater than 10.

In our Data Model, we create a Measure called [Sales Innovative Bulk Purchase (quantity > 10)] that contains the following formula.

``=CALCULATE([Total Sales], tblProduct[Type] = "Innovative", Sales[Quantity] > 10 )``

Adding this new Measure to our existing Pivot Table produces the following results.

If you’re having difficulty visualizing how these new values have been derived, check out the following Pivot Table.

In the above Pivot Table, we have added the [Quantity] field to the Rows section to break down each month/type by their respective quantities.

Next, we filter the [Quantity] column to only include entries that are greater than 10.

As with the previous Measure, when using CALCULATE, these new calculations are produced in the background using the same process.  The results are the only part of the process we can see.

## Bonus Tip: Using Measures Inside Other Measures

We saw where we used the [Total Sales] Measure in each of the CALCULATE Measures.

In the Measure where we are further filtering and calculating by [Quantity], we are repeating the same expression ([Total Sales]) and filter that reduces the Data Model by [Type] = “Innovative”.

``=CALCULATE([Total Sales], tblProduct[Type] = "Innovative", Sales[Quantity] > 10 )``

Our second CALCULATE Measure that filters by [Quantity] can use the first CALCULATE Measure as its expression, then additionally filter by [Quantity].

We’ll call this test Measure [Bulk v2].

``=CALCULATE([Sales Innovative Products], Sales[Quantity] > 10 )``

The results of [Bulk v2] are the same as [Sales Innovative Bulk Purchase (quantity > 10)].

This way if we must update either the [Total Sales] or [Sales Innovative Bulk Purchase (quantity > 10)] Measures, we only need to perform the update once and the changes will flow to all dependent Measures.

### Power BI CALCULATE

When you use the CALCULATE function in Power BI, you can modify how your data is aggregated or calculated by applying specific conditions or filters. This makes it incredibly versatile for creating dynamic reports and insights from your data.

Whether you want to see sales figures for a particular region, or compare this quarter’s profits to last quarter’s, the CALCULATE function helps you zero in on exactly what you need to know.

In short, it’s a key function for anyone looking to unlock the full potential of their data in Power BI.

### Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.