## Introduction

The previous part of our series looking at new tools for **working with accounting data** in Excel 2010 and 2013 touched on the use of DAX (Data Analysis Expressions) and, in particular, some of the DAX functions that relate to dates and time. As in the **previous article**, we will use the Invoices data from Microsoft’s sample database: Northwind. *[Our thanks to Microsoft for permission to use it in this tutorial. If you are not able to locate a copy of the 434kb zipped you can to **download a copy here**, **then extract Northwind.mdb].*

First of all, my apologies if everyone else finds all this stuff blindingly obvious. Hopefully, there will be at least a few people out there who will find the following explanations of some use. We are going to consider some of the concepts underlying the way DAX expressions work as measures within a PivotTable, before looking at the use of some different filter functions in particular.

*DAX*

As we pointed out last time, some of the more complex DAX functions combine Excel calculation functionality with database functionality. Some DAX functions work with columns or whole tables of data as they are arguments rather than just cell references.

*PivotTable values*

Another important concept when using DAX to create measures within a PivotTable is to understand that every cell in a PivotTable behaves like a database query rather than a simple calculation. A database query takes one or more tables of data as the input. One or more criteria can be applied to the input to restrict which rows are included in the output. A value in a PivotTable can be thought of as the result of a single column of a query based on a value field. All the other elements of a PivotTable can be thought of as criteria restricting the rows that are to be totalled in the value column.

Slicers and criteria fields are obviously filters, but all row and column headers are also criteria applied to the cells at each row/column junction. I found that, when working with some DAX functions, it can help to think of each PivotTable value in this way.

Let’s take a (relatively!) simple example. We can use the CALCULATE() function to apply filters to a calculation. The following expression will total the ExtendedPrice field applying the filter that the Country field is equal to ‘Argentina’:

=CALCULATE(SUM([ExtendedPrice]),Invoices[country]="Argentina"

If our PivotTable has no filters of its own, then the expression will take all the values in the ExtendedPrice column and add them up, if the value in the corresponding row of the Country column is equal to Argentina. Here is our resulting PivotTable:

Let’s add a field to the rows area and see what happens:

As we can see, the Salesperson ‘filter’ applied to each row is not overridden by our Calculated Field. Instead, both filters have to be satisfied for a value to be included in the calculation of each of our PivotTable cell totals.

We’ll replace the Salesperson field with the Country field:

This might be surprising. We might have expected both filters to have been applied as they were for the Salesperson field and therefore only to have seen a result for Argentina. However, the way CALCULATE() works, where a column is included as a filter, that filter overrides any other filters for the same column in the PivotTable.

We’ll look at the reverse of adding a filter in CALCULATE(). We can use the ALL() function to override existing filters.

=CALCULATE(SUM([ExtendedPrice]),ALL(Invoices))

We have used the entire Invoices table as the argument for ALL(), so any PivotTable filters based on Invoice table fields will be ignored:

So far, we have only proved that ALL() overrides the Country filter applied by the row labels. We can add the Salesperson as a Column label to see that the Column filter is also overridden:

There is also an ALLEXCEPT() function. This overrides all the PivotTable filters except the ones entered as the argument of the function. In the following expression we are overriding all the filters in the PivotTable apart from the Country filter

=CALCULATE(SUM([ExtendedPrice]),ALLEXCEPT(Invoices,Invoices[Country]))

Used in the following PivotTable, we can see that the Salesperson row filter is ignored in the AllExcept column but, the Country filter is applied:

Let’s try and put all of this to some sort of practical use. Perhaps we want to pay each of our sales representatives a 2% bonus if their total sales exceed £200,000. For costing purposes, we want to analyse our bonus payments across the different countries to which we have made sales. Our basic PivotTable might look like this, showing sales by country by salesperson:

We need to work out whether the total sales for a salesperson are greater than £200,000 and if they are multiply sales by country by 2%. Part of our calculation needs to ignore all filters apart from the Salesperson filter and part needs to abide by the PivotTable filters. We can use CALCULATE() and ALLEXCEPT() to achieve this:

=IF(CALCULATE(SUM(Invoices[ExtendedPrice]),ALLEXCEPT(Invoices,Invoices[Salesperson]))>200000,

SUM(Invoices[ExtendedPrice])*.02,0)

We have used IF() to compare our total sales, ignoring all PivotTable filters apart from the Salesperson filter, to our threshold figure. Where the threshold is exceeded we multiply the total sales – taking into account all PivotTable filters – by 0.02. Here is the result of adding our Bonus calculated field to our PivotTable:

In this section of the PivotTable we can see that the total sales figures for Janet Leverling and Margaret Peacock have exceeded our bonus threshold and we can also see the bonus earned split across the different countries. Because we have used the ALLEXCEPT() function to clear all filters apart from the Salesperson filter in calculating whether a bonus is payable, we could replace the Country field with any of our other fields to see the allocation of the bonus payments across other fields: products sold for example.