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.
