Tutorial: Sales analysis, part 4: Data analysis expressions (DAX) | AccountingWEB

# Tutorial: Sales analysis, part 4: Data analysis expressions (DAX)

## ntroduction

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 herethen 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...

Continued...

Tags

### DAX

Simon,

Are you running the Power Pivot add-in and entering your CALCULATE function in the measures grid of the Power Pivot window?

### Calculated fields

Hi Excelcrafter

Yes to the first, and no to the second, although you can indeed create calculated fields there too. I was actually using the Measures (2010)/Calculated Fields (2013) commands in the PowerPivot ribbon tab, rather than the PowerPivot window itself. I haven't come across a (straightforward) way of adding calculated fields to the Excel Data Model without the PowerPivot add-in, although I believe that there are some other tools that will do so.

For anyone not familiar with the equivalent option in the Data View of the PowerPivot window: at the bottom of each table there is a 'Calculation area' that you can choose to display that will list Calculated Fields for that table and allow you to create new calculated fields.

### DAX

Understood. I was wondering if you have found a way to add Calculated Fields to the Data Model (sans Power Pivot) as I haven't. Also Calculated Items (PivotTable Analyze tab)  that can be created on a regular PivotTable, is disabled on a PivotTable built off of the Data model. Not a problem if you have Power Pivot, but limiting if you don't.