PowerPivot’s CALCULATE function to the rescue

 Dec 16, 2013

One of the most commonly used DAX functions in PowerPivot is the ‘CALCULATE’ function, and in my opinion, it’s one of the most interesting functions because it’s a very flexible filter. It’s also easy to understand especially if you are familiar with ‘SUMIF’ or ‘SUMIFS’ functions in Excel. In Excel, the function SUMIF is used to sum up cells within a specified range that meet a certain criterion. SUMIFS do the same thing but with multiple criteria. When applying a SUMIF function, what you are indicating to Excel is that you want it to scan the specified range and pick up only those cells from this range that meet the criterion and then sum them up. Pretty straight forward, right? The good news is that Excel also provides its equivalent ‘AVERAGEIF’ and ‘COUNTIF’ so you can do average and count operations based on criteria. The bad news is that it stops there. There are no ‘MAXIF’ or ‘MINIF’ or any other IF functions. This is where PowerPivot’s CALCULATE function comes to the rescue. With the CALCULATE function, you can define your own operation over one or more criteria. For example:
The first argument SUM([Balance]) is the operation, and the second one FILTER(‘BankAccounts’,[Account]=”Savings”) is the criterion. The ‘BankAccount’ is the name of a table and [Account] and [Balance] are two of its columns. Here, we are asking to sum up Balances for Savings Accounts. The way the function works is that it first extracts a subset of the table using the criterion specified in the FILTER function. It will then extract those rows from the table whose [Account] field has the value “Savings” and apply the function SUM()  over the [Balance] field of this new sub-table. You could put any complex formula for SUM()and it would execute that formula over this sub-table. You can also incorporate more than one FILTER function, and if you do, the resulting table will be a combination of filters. These filters are combined when the criteria are ‘AND’-ed together. For example:
There is no explicit AND function here, but the two filters side-by-side indicate that both filters must apply. This function will sum up all the Balance for Savings accounts in Branch No. 101. The sub-table will be the rows that satisfy both filter criteria at the same time hence, the ‘AND’ operation. What you need to remember is that if the CALCULATE function is used in a PivotTable Measure, the filters in the function will filter data from a table that is constructed by the query and filter contexts of the PivotTable. Depending on which rows or columns you have added to the PivotTable, a table is created from the raw data first and then the CALCULATE function’s filter kicks in to further filter out that table to produce the resultant table. The CALCULATE function’s first argument will then apply to this table. Another thing to note is that there are filter functions that can be used inside the CALCULATE function, which can remove the filtering imposed by the PivotTable such as ‘ALL()’ and ‘ALLEXCEPT().’ It is important for users to understand the order of events in PowerPivot calculations before constructing their formulas. Usually PivotTable’s query context and filter context are applied first and then the filters inside the measure. So keeping all these things in mind, applying PowerPivot’s CALCULATE function is easy as 1-2-3!

How do your Excel skills stack up?   

Test Now  

About the Author:

Cyrus Mohseni  

Having worked within the education and training industry for over 15 years as well as the IT industry for 10 years, Cyrus brings to New Horizons a wealth of experience and skill. Throughout his career he has been involved in the development and facilitation of numerous training programs aimed at providing individuals with the skills they require to achieve formal qualification status. Cyrus is a uniquely capable trainer who possesses the ability to connect with students at all levels of skill and experience.

Read full bio