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

» Register now

The full article is available to registered AccountingWEB members only. To read the rest of this article you’ll need to login or register.

Registration is FREE and allows you to view all content, ask questions, comment and much more.

Comments

DAX

Excelcrafter | | Permalink

Simon,

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

shurst's picture

Calculated fields

shurst | | Permalink

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

Excelcrafter | | Permalink

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.

shurst's picture

Power Pivot add in

shurst | | Permalink

Yes, it's a good point to make, not least because of Microsoft's decision to limit availability of the PowerPivot addin (and some other new features such as PowerView and Inquire) to the Professional Plus edition of Excel unlike Excel 2010 when it was freely available. On this point, it appears that Standalone Excel will shortly include some or all of these add-ins, although several forum contributions seem to suggest that an installation bug prevents this at the moment.

DAX    1 thanks

Excelcrafter | | Permalink

It's also worth noting that Excel 2013 models built with the Power Pivot add-in, that use DAX functions, work fine in versions of Excel 2013 that support the Data Model, but without the add-in. I believe there is a 10 MB limit on the workbook size though. So potentially a user equipped with Power Pivot can create models for use by other users not so equipped.