Key Performance Indicators (KPIs) in Power Pivot (2024)

Key performance indicators (KPIs) are visual measures of performance. Supported by aspecific calculated field, a KPI is designed to help users quickly evaluate the current value and status of a metric against a defined target. The KPI gauges the performance of the value, defined by a Base measure (also known as a calculated field in Power Pivot in Excel 2013), against a Target value, also defined by a measure or by an absolute value. If your model has no measures, see Create a measure.

Here's a PivotTable with Employee Full Name in rows and Sales KPI in values.

Learn more about KPIs below, and then continue reading in the section that follows to see how easy it is to create your own KPI.

More about KPIs

A KPI is a quantifiable measurement for gauging business objectives. For example, the sales department of an organization might use a KPI to measure monthly gross profit against projected gross profit. The accounting department might measure monthly expenditures against revenue to evaluate costs, and a human resources department might measure quarterly employee turnover. Each of these is an example of a KPI. Various business professionals frequently group KPIs together in a performance scorecard to obtain a quick and accurate historical summary of business success or to identify trends.

A KPI includes a base value, a target value, and status thresholds.

Create a KPI

Follow these steps:

1. In Data View, click the table containing the measure that will serve as the Base measure. If necessary, learn how to Create a base measure.

2. Ensure that the Calculation Area appears. If not, then click Home > Calculation Areato display the Calculation Area appears beneath the table.

3. In the Calculation Area, right-click the calculated field that will serve as the base measure (value), and then click Create KPI.

4. In Define target value, select from one of the following:

   2. Select Measure, and then select a target measure in the box.

   3. Select Absolute value, and then type a numerical value.
      

      Note:If there are no fields in the box, there are no calculated fields in the model. You need to create a measure.

5. In Define status thresholds, click-and-slide to adjust both the low and high threshold values.

6. In Select icon style, click an image type.

   See Also

   The 5 Most Important Financial KPIs That Drive Business Strategy | FreshBooks Blog

7. Click Descriptions, and then enter descriptions for KPI, Value, Status, and Target.

Edit a KPI

In the Calculation Area, right-click the measure that serves as the base measure (value) of the KPI, and then click Edit KPI Settings.

Delete a KPI

In the Calculation Area, right-click the measure that serves as the base measure (value) of the KPI, and then click Delete KPI.

Remember, deleting a KPI does not delete the base measure or target measure (if one was defined).

Example

The sales manager at Adventure Works wants to create a PivotTable she can use to quickly display whether or not sales employees are meeting their sales quota for a specific year. For each sales employee, she wants the PivotTable to display the actual sales amount in dollars, the sales quota amount in dollars, and a simple graphic display showing the status of whether or not each sales employee is below, at, or above their sales quota. She wants to be able to slice the data by year.

To do this, the sales manager chooses to add a Sales KPI to the AdventureWorks workbook. The sales manager will then create a PivotTable with the fields (calculated fields and KPI) and slicers to analyze whether or not the sales force is meeting their quotas.

In Power Pivot, a calculated field on the SalesAmount column in the FactResellerSales table, which gives the actual sales amount in dollars for each sales employee is created. This calculated field will define the Base value of the KPI. The sales manager can select a column and click AutoSum on the Home tab or type a formula in the formula bar.

The Sales calculated field is created with the following formula:

Sales:=Sum(FactResellerSales[SalesAmount])

The SalesAmountQuota column in the FactSalesQuota table defines a sales amount quota for each employee. The values in this column will serve as the Target calculated field (value) in the KPI.

The SalesAmountQuota calculated field is created with the following formula:

Target SalesAmountQuota:=Sum(FactSalesQuota[SalesAmountQuota])

Note:There is a relationship between the EmployeeKey column in the FactSalesQuota table and the EmployeeKey in the DimEmployees table. This relationship is necessary so that each sales employee in the DimEmployee table is represented in the FactSalesQuota table.

Now that calculated fields are readyto serve as the Base value and Target value of the KPI, the Sales calculated field is extended to a new Sales KPI. In the Sales KPI, the Target SalesAmountQuota calculated field is defined as the Target value. The Status threshold is defined as a range by percentage, the target of which is 100% meaning actual sales defined by the Sales calculated field met the quota amount defined in the Target SalesAmountQuota calculated field. Low and High percentages are defined on the status bar, and a graphic type is selected.