WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
Wise Owl Training
See 520 reviews for our classroom and online training
If you found this blog useful and youâ€™d like to say thanks you can click here to make a contribution. Thanks for looking at our blogs!

BLOGS BY TOPIC

BLOGS BY AUTHOR

BLOGS BY YEAR

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:

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:

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!