Power Pivot Principles: Using Measures to Define Target Value in KPIs

Welcome back to the Power Pivot Principles blog. This week, we will talk about using measures to define target values for KPIs.

Last week, we introduced the Key Performance Indicator functionality in Power Pivot, which is a quantifiable measurement for gauging business objectives.

In last week’s example, we had the SalesData table, with sales by stores and by product types, in the first quarter of FY20 / 21, which has already been loaded into the Power Pivot Data Model:

Then, we created a KPI by comparing the Total Sales of each store with the target sales defined by an absolute value of $30,000:

To make the KPI more dynamic, we may consider using a measure as the target value.  In particular, we want to compare Total Sales of each store against the Average Sales by Store.  First, we need to create the Average Sales by Store measure, by dividing the Total Sales (using the DIVIDE function) by the number of unique stores (using the DISTINCTCOUNT function):

In the PivotTable, the Total Sales is now shown against the Average Sales by Store:

To adjust the KPI, navigate to the Power Pivot tab on the Ribbon, choose KPIs - > Manage KPIs…, and then the Key Performance Indicator dialog will appear. Under the ‘Define target value’ section, we select Measure and choose ‘Average Sales by store’ from the dropdown menu. We can also change the status thresholds and icon style to our liking:

Now, we can see the Total Sales Status with icons indicating the sales performance of each store compared to the Average Sales by store:

That’s it for this week!


Stay tuned for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.