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

## 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:

- The visual’s filters are applied. These can come from a Pivot Table’s
*rows/columns/filters*or other visualization, like a Slicer. - Additional filters from the
**CALCULATE**function are added. - If a filter used by
**CALCULATE**already exists in the visual, the visual’s filter is replaced by**CALCULATE**’s filter. - 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)

## 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 **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 **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.

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

Now we will write a Measure using **CALCULATE** 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.

### 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.