BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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.
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:
- 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.
- 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.
- Decide what symbols you want to display.
- Click OK.
You can now view your KPIs!
Inspecting your KPIs
For our data, this is what you'd see for the choices above:
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!