
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:CALCULATE(SUM([Balance],FILTER(‘BankAccounts’,[Account]=”Savings”))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:
CALCULATE(SUM([Balance],FILTER(‘BankAccounts’,[Account]=”Savings”)),FILTER(‘BankAccounts’,[Branch]=101))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 NowNext up:
- The impact of discounting and why you should not be offering it
- High Availability in Lync Server 2013
- The truth about Santa Claus
- Happy New Year!
- Work offline with SharePoint Workspace 2010
- Creating Effective Lists and Tables in Excel
- Cast out that colourcast in Photoshop
- Power View just got better!
- How to do an IF statement in Excel
- PowerShell’s Remote Control in a Nutshell
Previously
- Manipulate dates with custom number formats in Excel 2010
- The Pros and Cons of SSAS Tabular Models in SQL Server 2012
- Customising Layouts in SharePoint Designer 2010
- Deploying Web and Service Applications to Windows Azure
- A Knotty Issue
- Quoting in PowerShell for newbies
- How to integrate OneNote and Outlook
- Support the Salvation Army Christmas Appeal
- What’s new in Windows 8.1 for Developers
- Centre objects in Photoshop CS6 with precision