ICAEW.com works better with JavaScript enabled.

Excel Tip of the Week

Excel Tip of the Week #399 - Power Pivot - KPIs

Author: David Lyford-Smith

Published: 17 Jun 2021

Exclusive content
Access to our exclusive resources is for specific groups of students, users, subscribers and members.

Hello all and welcome back to the Excel Tip of the Week! This week we have a Creator post in which we are going to look at how to build visual KPIs into PivotTables created with Power Pivot.

The example we are going to use builds directly from where we left off in TOTW #369, so you might want to refresh yourself on that before we begin here.

What is a KPI?

A KPI, or Key Performance Indicator, is a familiar business term for a particular metric that is useful for understanding how our business is going.  But in Power Pivot terms specifically, KPIs are associated with Measures, and allow us to apply some automatic visual cues to support those Measures.

Let’s take a look at a particular Measure which we calculated last time, our TotalSalesValue, and how that varies across time:

Excel screenshot
This simple Measure just calculates the value of our sales. But let’s say we want to compare the monthly sales to our internal targets. We can do this from the Power Pivot ribbon > KPIs interface:
Excel screenshot

This launches a menu from which we can design our KPI, and define how our success will be measured. The target can be a stated value, or defined based on another Measure. You can also set thresholds for different traffic-light values that will be shown against the measure. Here’s a look at our definition:

Excel screenshot

If you use a Measure to determine the target instead, you can set the grading according to a percentage of that Measure. You can also see in the bottom right that different gradings are available – the default is “low bad, high good” but you can reverse this, or set boundaries for KPIs that should be near or far from a central figure. There are also several different options for icons – we’ll see how that comes in shortly.

Once our KPI is defined, you can then find it and add to the table in the usual PivotTable Fields menu; the Measure itself is replaced with a menu showing the KPI options for this Measure:

Excel screenshot

Value is the base Measure we are applying; Goal is the target value we set earlier, and Status is the symbol associated with the current month based on values.  If we add to our Pivot, this is what we see:

Excel screenshot
Excel screenshotExcel screenshot