Create a Measure and KPI (Tutorial)

Looking for help with Power Pivot in Excel 2013? Go to Power Pivot Help on Office.com.

In this lesson you will use PowerPivot to create and manage a measure and a Key Performance Indicator. A measure is a formula that is created specifically for use in a PivotTable (or PivotChart) that uses PowerPivot data. Measures can be based on standard aggregation functions, such as COUNT or SUM, or you can define your own formula by using DAX. For more information about measures, see Measures in PowerPivot.

A Key Performance Indicator (KPI) is based on a specific measure and is designed to help evaluate the current value, status, and trend of a metric. The KPI gauges the performance of the value, defined by a Base measure, against a Target value. For more information about KPIs, see Key Performance Indicators (KPIs) in PowerPivot.

Prerequisites

This topic is part of a PowerPivot for Excel tutorial, which should be completed in order. For information about prerequisites and the first step in the tutorial, see PowerPivot for Excel Tutorial Introduction.

Measures

In this tutorial, you will create one measure that calculates store sales, a second measure that calculates last year store sales, and a third measure that uses both of the previous measures to calculate year over year growth. You will use this last measure as the basis for a KPI that indicates whether annual growth is above, at, or below target. Creating a measure is a requirement for creating a KPI.

Note

Measures used in this lesson are borrowed from the “Year Over Year Growth” scenario in the PowerPivot DAX Survival Guide. For more information, see PowerPivot DAX Survival Guide.

To create a measure that calculates store sales

  1. In Data View of your PowerPivot window, click the FactSales table tab at the bottom of the window. In practice, you can place measures in any table, but for simplicity we will use the FactSales table as the logical home for all of the aggregations we create.

  2. Show the Calculation Area. The Calculation Area is a grid at the bottom of each table. It contains any implicit or explicit measures that you create. To display the Calculation Area, click Calculation Area in the Home tab.

  3. Click the first cell in the Calculation Area. It happens to be under the SalesKey column. The measures that you are creating are independent of any column in the table. We choose the first column in the grid for convenience, to more easily see our measures without having to scroll through the grid.

  4. In the formula bar, type the name StoreSales.

  5. Next type a colon, and then begin to type the =CALCULATE() formula. As you type, the related formulas appear below the formula bar.

  6. Double-click the CALCULATE formula. The formula populates as =CALCULATE in the formula bar. CALCULATE(Expression, [Filter1], [Filter2], …) appears below the formula bar.

  7. Begin to type SUM. Double-click SUM when auto-complete displays it.

  8. Type FactSales[SalesAmount]), DimChannel[ChannelName]=”Store”) to complete the formula.

  9. Compare your formula the following formula. Pay close attention to the placement of parentheses and brackets to avoid syntax errors:

    StoreSales:=CALCULATE(SUM(FactSales[SalesAmount]), DimChannel[ChannelName]="Store")
    
  10. Press Enter to accept the formula.

To create a measure that calculates last year’s sales

  1. In the Calculation Area, beneath the SalesKey column, click the second cell from the top (under StoreSales), and then in the formula bar, paste in the following formula:

    StoreSalesPrevYr:=CALCULATE([StoreSales], DATEADD(DimDate[Datekey], -1, YEAR))
    
  2. Press Enter to accept the formula.

To create a measure that calculates year-over-year growth

  1. In the Calculation Area, beneath the SalesKey column, click the third cell from the top (under StoreSalesPrevYr), and then in the formula bar, paste in the following formula:

    YOYGrowth:=([StoreSales] - [StoreSalesPrevYr]) / [StoreSalesPrevYr]
    
  2. Press Enter to accept the formula.

    You should now have three measures to use as the basis for your KPI. In the next step, you will format each measure so that the values are more readable in the workbook.

To format each measure

  1. In the Calculation Area, beneath the SalesKey column, right-click StoreSales and then select Format.

  2. In the Formatting dialog box, select Currency and then click OK.

  3. Right-click StoreSalesPrevYr, select Format, select Currency and then click OK.

  4. Right-click YOYGrowth, select Format, select Number, and then choose Percentage. Click OK.

KPIs

One of the requirements for creating a Key Performance Indicator (KPI) is to first create a base measure that evaluates to value. You will then extend the base measure to a KPI. In this tutorial, you will create a KPI based on the last measure you created, YOYGrowth. You will use this measure to add thresholds that indicate whether store performance relative to last year is on target, below target, or at target.

To create a KPI

  1. Make sure you are in the Data View of the FactSales table. If the Calculation Area is not displayed, on the Home tab, click Calculation Area.

  2. In the Calculation Area, under the SalesKey column, right-click the YOYGrowth measure, which will serve as the base measure (value). Because this measure is a percentage, you will use absolute values to indicate whether the percentage is above or below target.

  3. In the measure’s context menu, click Create KPI (or you could click Create KPI on the Home tab in the Measures area). The Key Performance Indicator (KPI) dialog box appears.

    Note

    Create KPI is only available for measures that you create using the approaches previously described. If you create a measure in Excel, by dragging a field from a table to the Values area in the PowerPivot field list, that measure is an implicit measure and cannot be used as the basis of a KPI. For more information, see Measures in PowerPivot.

  4. In Define target value, select Absolute value, and then type 0.

  5. In Define status thresholds, click and slide the low threshold value to -0.05 and the high value to 0.05.

    The status thresholds indicate that 5% negative growth marks the low range, and 5% positive growth marks the beginning of the high range.

  6. In Select icon style, click the traffic lights icon style.

  7. Under Select icon style, click Descriptions, and then type Tutorial example in the KPI description box.

  8. Click OK to create the KPI. The KPI icon appears on the right side of the YOYGrowth cell in the Calculation Area.

Next Step

In the next lesson, Create a Perspective (Tutorial), you will use the measures and KPI you just created in a PivotTable that is based on a perspective. A perspective is a subset of tables and columns. As you will see, using a perspective simplifies report authoring by removing items that are not relevant to your analysis.

See Also

Tasks

PowerPivot for Excel Tutorial Introduction

Concepts

Key Performance Indicators (KPIs) in PowerPivot

Measures in PowerPivot

Create a Measure in a PivotTable or PivotChart

Edit or Rename a Measure in a PivotTable or PivotChart

Delete a Measure in a PivotTable or PivotChart

What's New in PowerPivot