# 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

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.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.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.In the formula bar, type the name

**StoreSales**.Next type a colon, and then begin to type the

**=CALCULATE()**formula. As you type, the related formulas appear below the formula bar.Double-click the

**CALCULATE**formula. The formula populates as**=CALCULATE**in the formula bar.**CALCULATE(Expression, [Filter1], [Filter2], …)**appears below the formula bar.Begin to type

**SUM**. Double-click SUM when auto-complete displays it.Type FactSales[SalesAmount]), DimChannel[ChannelName]=”Store”) to complete the formula.

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")`

Press Enter to accept the formula.

### To create a measure that calculates last year’s sales

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))`

Press Enter to accept the formula.

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

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]`

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

In the Calculation Area, beneath the

**SalesKey**column, right-click**StoreSales**and then select**Format**.In the Formatting dialog box, select

**Currency**and then click**OK**.Right-click

**StoreSalesPrevYr**, select**Format**, select**Currency**and then click**OK**.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

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**.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.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.In

**Define target value**, select**Absolute value**, and then type 0.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.

In

**Select icon style**, click the traffic lights icon style.Under

**Select icon style**, click**Descriptions**, and then type**Tutorial example**in the**KPI description**box.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

Create a Measure in a PivotTable or PivotChart

Edit or Rename a Measure in a PivotTable or PivotChart