DAX CALCULATE Function Explained

The CALCULATE function is a very powerful DAX function, which can modify the visual filters which is another way of saying, it has the power to modify the filter context.

 

Grab a quick video on CALCULATE

To understand how the CALCULATE function works you should first understand – what is filter context? Here is a quick one liner on Filter Context..

Filter Context is the set of filters applied to the data before the DAX Calculation is carried out

CALCULATE Syntax

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

1. Expression – can be a measure or a calculation that you’d want to evaluate. Make sure that the expression should result into a scalar value.
2. Filters – The second part are the set of Optional Filters. While inputting filters consider three things.
   • These filters need to evaluate as a table. They cannot be scalar values.
   • CALCULATE function filters will always override the filters from the Visuals.
   • The CALCULATE filters should not ideally clash with one another.

 

CALCULATE Function Example

Consider this simple data model

1. The Sales table is related to the Calendar (Date Table)
2. The Sales table has standard columns like Date, Product ID, Sales, Trans ID and Channel

Now see the Pivot Table and a simple measure Total Sales

Total Sales = SUM( Sales[Sales] )

Consider another measure exclusively for calculating Affiliate Sales and it’s result

Affiliate Sales = 
CALCULATE(
    [Total Sales],
    Sales[Channel] = 'Affiliate'
)

1. You should be fairly surprised to see Affiliate Sales Value 20,368 against “Organic” and “Promotional” channels.
2. In fact the same value gets repeated for Total as well.
3. It is common to expect a blank against Organic and Promotional

What is wrong here? Nothing.. that’s the behavior of CALCULATE. Let’s explore this.

 

How does the CALCULATE Function Work

I’ll be referring the previous example here

1. Consider 20,368 against Channel = “Affiliate”.
   • Pivot table applied a filter, Channel = Affiliate and CALCULATE Function also applied the same filter Channel = “Affiliate”
   • Since there is no clash between visual filter (from the Pivot table) and CALCULATE function filter, the result is correct
2. Now Consider 20,368 against Channel = “Organic”
   • Pivot table applied a filter, Channel = “Organic” but the CALCULATE function still applies a filter Channel = “Affiliate”.
   • Since the filter from the Pivot Table clash with CALCULATE filter, CALCULATE filter overrides the Pivot Table filter and applies Channel = “Affiliate” and shows the Affiliate sales value against Organic (i.e. 20,368).
   • Same process gets repeated for the Channel = Promotional and Total as well.

Although this behavior might seem weird at start but this is quite a game changer.

 

Application of CALCUALTE Function

A practical problem could be to calculate %contribution of each channel over Total Sales i.e. dividing sales from each channel with total sales to calculate percentage contribution.

Consider this measure

All Channel Sales =
CALCULATE(
   [Total Sales],
   ALL(Sales[Channel])
)

1. The ALL Function ignores the Filter from the Pivot Table and takes the Total Sales Value against each Channel.
2. Now we can divide Total Sales by All Channel Sales to get % Contribution.

% Sales = 
DIVIDE(
   [Total Sales], 
   [Total Sales of all channels]
)

 

Another Application of CALCULATE

Let's solve another practical problem where you can use the behavior of CALCULATE function. I have created a pivot table with year and months placed against Total Sales.

Consider calculating growth % over last year for each month. In order to calculate that, two steps are required.

1. Find Sales Value of the each month from Last Year
2. Current Year Sales / Last Year Sales minus 1 (that gives us the growth)

See these 2 measures that calculate the Last Year Sales and then subsequently the % Growth

LYSales =
CALCULATE(
   [Total Sales],
   SAMEPERIODLASTYEAR('Calendar'[Date])
)

PctGrowthLY =
IF(
   [Total Sales] <> BLANK() &&
   [LYSales] <> BLANK(),
   [Total Sales] / [LYSales] - 1
)

If you are thinking.. of course you can convert the growth to a % format. I am a bit lazy 😐

And one more thing, while calculating Last Year Sales at the total level, it’s ideal to compare the same number of months from the last year for which the sales happened in the current year. I have solve that problem here 🙂

 

 

More on DAX Functions

1. CALCULATE Function Tricks for Non Blank Values
2. EARLIER Function
3. KEEPFILTERS Function
4. DAX Functions Playlist on Youtube