Excel PowerPivot Training Courses

Unit 11 – A selection of DAX Functions 

In this unit, you will learn how to: 

CALCULATE() 

Have you ever used the Excel function SUMIF(), or perhaps its newer cousin, SUMIFS()? 

I describe CALCULATE() as “the SUMIF/SUMIFS you always wish you’d had.”  You are going to love this function, because it works wonders. 

In case you are one of the pivot pros who managed to skip SUMIF() and SUMIFS() in normal Excel, they are both very useful functions: they sum up a column you specify, but filter out rows that don’t fit the filter criteria you specify in the formula. So, for instance, you can use SUMIF to sum up a column of Sales figures, but only for rows in the table where the Year column contains 2012. 

Does that sound familiar? It sounds a lot like the Golden Rules from the prior chapter – “filter, then arithmetic.” An interesting similarity, and CALCULATE() continues in that same tradition. 

Anyway, CALCULATE() is superior to SUMIF() and SUMIFS() in three fundamental ways: 

CALCULATE(<measure expression>, <filter1>, <filter2>, …) 

Ex:  CALCULATE(SUM(Sales[Margin]), Sales[Year]=2001) 

Ex:  CALCULATE([Sales per Day], Sales[Year]=2002, Sales[ProductKey]=313) 

Start with a simple pivot. Year on rows, [Total Sales] measure on values: 

OK, let’s add a new measure, one that is always filtered to Year=2002: 

CALCULATE([Total Sales], Sales[Year]=2002) 

The results are shown below; 

Do those results surprise you? I bet they are close to what you expected, but maybe not exactly. You might have expected years 2001 and 2003 to display zeroes for our new measure, and you might be scratching your head a bit about the grand total cell, but otherwise, having the new measure always return the 2002 value from the original measure is probably pretty instinctive. 

It’s not very often that I write a CALCULATE measure that filters against a column that is also on the pivot (Sales[Year] in this case). That seldom makes any real-world sense. I just started out like this so you can see that the $6,530,344 number matches up. 

So, to make this a bit more realistic, let’s take Year off of the pivot and put MonthNum on there instead: 

This probably makes even more sense than the prior pivot. The grand total is still that $6.5M number, but every other cell returns a distinct number – the sales from 2002 matching the MonthNum from the pivot. 

How CALCULATE() Works 

Now that we’ve looked at a couple of examples, let’s examine how CALCULATE() truly works, because that will clear up the handful of somewhat unexpected results in that first example. 

There are three key points to know about CALCULATE(), specifically about the <filter> arguments: 

Examples of CALCULATE() 

The [2002 Sales] measure that I have been using as an example so far is a good way to show you how CALCULATE() works, but it might not seem terribly useful. So, let me show you two quick examples that are much more broadly applicable. 

Transactions of a Certain Type 

Here is one that I see all the time in the retail sales business: not all transactions are normal sales. Some businesses record many different transaction types including “Normal Transaction,” “Refund,” and “Promotional Sales Transaction.” 

My database has a column for that, so I went ahead and imported it into my Sales table (using Table Properties). Here, we see that it has three values: 

I now want to write four new measures, defined here in English: 

Now, here are the formulas for each: 

[Regular Sales] =CALCULATE([Total Sales], Sales[TransType]=1) 

[Promo Sales] =CALCULATE([Total Sales], Sales[TransType]=3) 

[Refunds] =CALCULATE([Total Sales], Sales[TransType]=2) * -1 

[Net Sales] =[Regular Sales] + [Promotional Sales] + [Refunds] 

The results are displayed below: 

Let us continue down the Practical Road, let’s see what percentage of our sales are due to us running promotional campaigns: 

[Pct Sales on Promo] =[Promo Sales] / ([Regular Sales] + [Promotional Sales]) 

Results: 

Mathematical Operator in <filters> 

In a <filter> argument to CALCULATE(), you are not limited to the “=” operator. You can also use: 

Evaluation of Multiple <filters> in a Single CALCULATE() 

All of the <filter> arguments in a single CALCULATE() behave as if they are wrapped in an AND() function. 

In other words, a row must match every <filter> argument in order to be included in the calculation. 

If you need an “OR()” style of operation, you can use the “||” operator.  For instance: 

CALCULATE([Total Sales], Sales[TransType]=1 || Sales[TransType]=3) 

ALL() – The “Remove a Filter” Function 

The Basics 

The ALL() function is used within a CALCULATE(), as one of the <filter> arguments, to remove a filter from the filter context. Maybe we want to analyse each monthly net sales figure against the total sales ignoring the year filter applied by the year slicer. 

Let’s jump straight to an example.  Consider the following pivot: [Net Sales] displayed by MonthNum, with Year on a slicer: