An overview of PowerPivot for Excel 2010
Part six of a six-part series of blogs

PowerPivot is a superb addition to Excel: it allows you to create pivot tables based on multiple tables taken from a variety of data sources, and its DAX language lets you report any statistic you want. This overview summarises how to get started with PowerPivot, and what it does.

  1. Getting started with PowerPivot for Excel 2010
  2. Stage 1 - Importing the tables we need
  3. Stage 2 - Creating a data model
  4. Stage 3 - Creating a Pivot Table
  5. Stage 4 - creating measures using DAX
  6. Stage 5 - KPIs in PowerPivot for Excel (this blog)

Posted by Andy Brown on 31 January 2013

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Stage 5 - KPIs in PowerPivot for Excel

A KPI is a Key Performance Indicator.  Typical business aims might be:

  • to try to exceed last year's sales by 10%; or
  • to generate a profit margin of above 6%.

Our KPI is going to be a bit more modest: we'll set a target that each director's contribution to the number of films should be 50%.

This is a ridiculous KPI, particularly as it's measuring historic data over which we have no control, but it will allow me to show how KPIs work in PowerPivot.

Components of a KPI

A KPI has 3 parts:

Part of KPI Notes
Base measure What we're measuring.
Target What we'd like it to equal.
Status How we're doing against target.

Creating a KPI

You can right-click on any measure to make it into a measureable KPI:

Creating KPI

Right-click on the Proportion measure created in the previous part of this blog to make it our KPI.

 

The next stage is to say what your target is, and how you'll display your status:

Create KPI dialog box

Complete the numbered steps as shown below.

The numbered steps are:

  1. Decide what your target is.  This can either be another measure, or (as here) an absolute value.  Here we're aiming for 50%, or 0.5.
  2. Define thresholds for the status: up to 20% we'll show in red; 20 to 40% will appear yellow; and anything above that will be green.
  3. Decide what symbols you want to display.
  4. Click OK.

You can now view your KPIs!

Inspecting your KPIs

For our data, this is what you'd see for the choices above:

KPIs by director and decade

Only SteveN Spielberg is consistently hitting his target!

The beauty of KPIs in PowerPivot is that they will always be measured for the current filter context (so you can see how your profit margin target is doing by region, by manager, by product, by month ...).

There's lots more to PowerPivot than I've shown here, but I hope this has given a flavour of this superb application.  I recommend it to anyone who currently uses pivot tables or analyses data in Excel!

This blog has 0 threads Add post