Tutorial: Sales analysis part 3 - create a dashboard

This is the fifth part of our series looking at new tools for  Working with accounts data in Excel 2010 and 2013 and the third of three articles looking at the analysis of transaction data. This time we will look at some of the features available to help us turn our data from a list of invoices into an interactive dashboard, including the incorporation of KPIs.

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

DAX

Before we explore the KPI feature built into the Excel 2010 and 2013 PowerPivot Add-in, we will have a look at some DAX formulae. DAX stands for Data Analysis Expressions and is the PowerPivot equivalent of Excel functions. The use of Many DAX functions is identical to their Excel equivalent – for example YEAR() and MONTH() to return elements of a date. However, DAX goes far beyond the standard Excel function capability and includes a comprehensive range of functions dedicated to working with database data rather than data in cells.

One of the most significant areas to appreciate when getting to grips with the more complex DAX functions is that they combine familiar Excel calculation functionality with database functionality. Rather than using values or cells as arguments, some DAX functions will use tables or columns of data.

Many DAX functions are designed to work with dates and times. For some of these to work reliably, they need to work with unbroken sequences of dates. For this reason, when working with DAX and dates in PowerPivot, it’s a good idea to create a table that includes all possible dates from the first date covered by the data to the last.

In the following example we have just added a new worksheet and copied a cell containing 01/01/04 – the first day of the first year in our Northwind invoices data set, down to 31/12/13. We’ve turned this into a Table called allDates and used the PowerPivot ribbon tab, Tables group, Add to Data Model command to add this to our PowerPivot data model. We’ve then used the Manage button to open the PowerPivot window, and used the simple DAX MONTH() and YEAR() functions to add two columns to our allDates table:

Then we switch to the Diagram view and connect our two tables, joining the OrderDates field to the Dates field:

We can then use the PivotTable button to create one or more PivotTables or PivotCharts. We have just chosen to create a single PivotTable on a new worksheet:

We have added the ExtendedPrice field from the Northwind invoices table to the VALUES area and the Year and Month fields from allDates as the ROWS.

We now want to add a column to show the equivalent figure from the same period last year. We can add Calculated Fields from the PowerPivot ribbon tab, Calculations group, Calculated Fields command. This is our Previous period field:

The formula uses 3 DAX functions:

=CALCULATE(SUM(Table_Northwind_meeb[ExtendedPrice]),
SAMEPERIODLASTYEAR(allDates[Dates]))

SUM() works in the same way as the normal Excel SUM() function

CALCULATE() evaluates an expression taking into account one or more filters. In this case, we want to return the value for the same period last year, so our filter will be a column of all the dates for the period applied to the cell in our PivotTable, but for the previous year. So, for the PreviousPeriod field in the Year 2005, Month 8 row the filter will be a column containing all the dates in Month 8, 2004.

SAMEPERIODLASTYEAR() converts a column of dates into the same dates in the previous year.

Here is the result of adding our Previous period field to our PivotTable. We can see how our DAX formula works for both month and year rows:

KPI

We are going to use this Previous period field as the basis of a KPI comparing the results in one year to the results in the same period in the previous year. We need to base our KPI on a Calculated Field, so we set up a Calculated Field equal to our ExtendedPrice field that we will call Total:

=SUM(Table_Northwind_meeb[ExtendedPrice])

Now we’ll go to the KPIs command also in the Calculations group of the PowerPivot ribbon tab and choose New KPI:

We will use the new Total field as our KPI base field and we have set our target value to our Previous period field. We can use the sliders to set our thresholds for the different icons and choose different types of icon.

Now we will add various fields related to our KPI to our PivotTable:

The Status column uses the Icons and thresholds we have chosen to highlight the change in sales between one period and the same period for the previous year.

Dashboard

To finish, we’ll use the PowerPivot window, Home ribbon, PivotTables option to create Four Charts based on the same set of data. We’ve set up each chart to show a particular aspect of our sales data and then added a Timeline slicer. We can right-click on this Timeline Slicer and use the Report Connections option to link our Timeline to each of our four charts:

We can select where we want to include additional PivotCharts or PivotTables and use the PowerPivot windows PivotTable button to add further PivotCharts and PivotTables. Here we have added a PivotTable that incorporates our KPI status and also used some CUBE() functions to include individual Salesperson figures, linked to our Timeline slicer:

Further reading and tutorials

• Working with accounts data in Excel 2010 and 2013
• Sales analysis in Excel 2010/2013, part 1
• Sales analysis in Excel 2010/13, part 2 
• Excel 2013: Where the productivity gains are
• Excel 2013: Up close and personal
• Excel 2010: Simon Hurst’s greatest tips
• Excel FAQs: Working with accounts data
• Improve your reporting skills with self-teach tutorials
• Improving sales reports at Northwinds Traders (first of a 2007 tutorial series by David Carter)
• AccountingWEB pivot table tutorial archive