Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

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

by
12th Sep 2013
Save content
Have you found this content useful? Use the button above to save it to your profile.

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:

Basic ExtendedPrice CALCULATE forumula

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

Salesperson filter applied to CALCULATED formula in pivot table

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:

Country field in Excel pivot table analysis of Northwinds data

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:

ALL function overrides the Country figures in pivot table fields

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:

Country filter applied

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:

Salesperson row filter is ignored, but country 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:

Bonus calculation applied using IF command in pivot table

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.

Replies (5)

Please login or register to join the discussion.

avatar
By Excelcrafter
13th Sep 2013 20:49

DAX

Simon,

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

Thanks (0)
Simon Hurst
By Simon Hurst
14th Sep 2013 09:45

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.

Thanks (0)
avatar
By Excelcrafter
14th Sep 2013 15:33

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.

Thanks (0)
Simon Hurst
By Simon Hurst
14th Sep 2013 16:00

Power Pivot add in

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.

Thanks (0)
avatar
By Excelcrafter
14th Sep 2013 16:14

DAX

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.

Thanks (1)