Understanding the CALCULATE  function in DAX

The CALCULATE function is pivotal in DAX for modifying the context in which data is evaluated. 

By using CALCULATE, you can perform calculations on your data model by applying filters.

CALCULATE in DAX is sort of the equivalent of COUNTIF  and SUMIF in Excel, but much more powerful

It has no limits to the aggregation functions that you can use (MIN,MAX, AVERAGE, STATISTICAL,....)

The CALCULATE function has the following syntax

CALCULATE(<expression>, <filter1>, <filter2>, ...)

<expression>: This parameter represents the expression or calculation you want to evaluate. It could be an aggregate function like SUM, AVERAGE, COUNT, or any other valid DAX expression.

<filter1>, <filter2>, ...: These parameters are the filters you want to apply to modify the context in which the expression is evaluated. Filters can be conditions that you want to impose on the data to narrow down the results.

Let's start from a simple table in Excel on which we create a measure using Calculate

A previous post shows how to create Measures in Excel using the DAX language

The measure created is called SUmOfQuantitySouth and is equal to:

SumOfQuantitySouth = CALCULATE(sum([Quantity]),'Table4 1'[Region] = "South")

This measure sums all the quantities for which the column 'Region' reports 'South'

In this case it will return the value of 50 as the Sum of all quantities reporting 'South' in the 'Region' column is equal to 50.

As it is a measure it shows up in the menu for building the Pivot Tables 

How does the measure behave in a Pivot Table?

1) The value used for filter in Calculate overrides all other values in the same column

If in the rows or columns field of the Pivot Table are inserted values used for the filters in the Calculate function, the measure will display the same value in all cells of the Pivot Table. 

Let's make an example

In the rows section of the above pivot table we have inserted the 'Region' column.

In the values field we have inserted the  SumOfQuantitySouth measure.

As the column reported in the rows sections is also the column used as a filter in the mmeasure the values remain the same for all the cells. 

The logic of the SumOfQuantitySouth overides the filters in the Pivot Yable. Therefore the value does not change even when the corresponding value in the rows is not 'South'

2) Pivot Table filters, placed on rows or columns,  not used as a filter in the calculate measure will display only if related to the measure

The below image shows the same Pivot Table where the 'Product' has been inserted in the pivot table columns. 

Only product B shows in the columns as it is the only product that has been sold in the 'South' region and therefore matching the filters of the SumOfQuantitySouth measure.

The 50 value shows in correspondence of the South and North region as the are the only reasons that have sold product B

This post has explained how to use the calculate function to build measures in Pivot Tables

It is important to be aware of the particular behaviors the CALCULATE function has when inserted in a pivot table in order to perform more comprehensive data analysis