Simon Hurst advises how Excel’s PowerPivot add-in can streamline your dashboard measures.
Tutorial 3 in this series looked at PowerPivot dashboards. An updated “release candidate” version of PowerPivot known as “Denali” was released late last year that includes some significant new features and improvements. One of the new features is a KPI section in the PowerPivot ribbon.
The idea of a PowerPivot KPI is to create a simple visual guide to how well one measure added to your PivotTable compares to an absolute value or a second target measure. When any cell in your PivotTable report is selected, the New Measure button on the PowerPivot ribbon’s Measures group should be enabled. Click on this to create a new measure as the base for your KPI.
Having created a measure, the Create KPI option should be enabled. However, if we want to base our KPI status on another measure, rather than an absolute value, then we will need to create a second measure to function as our target.
Just in case you were wondering, you can’t include a direct reference to a cell value as part of the measure formula. If you did want to be able to easily select particular target values without changing your measure formula, one method is to link an Excel table containing a list of the possible target values to the PowerPivot model, then use it as a Slicer. Your measure formula could refer to this linked Excel table and selecting a value in the Slicer would set the value of the table to the selected value.
With the base measure of “Sales” and our target “Sales target” measure we can create our KPI which will show how each salesperson’s sales compares to 10% of overall sales. We have set the red icon for sales up to 50% of our target, amber from 50% to 100% and green for anything over our target. Here is the result:
It would be possible to do something similar without the new KPI feature using the icon sets within conditional formatting, but the new feature does make the process more obvious and easier to set up and manage.
About the author Simon Hurst is a former chairman of the ICAEW IT Faculty and runs The Knowledge Base, a consultancy dedicated to helping accountants make effective use of technology. He is a regular contributor to AccountingWEB's ExcelZone and the author of '100 Time-saving Tips for Microsoft Office'. For more information, visit The Knowledge Base website.