Key performance indicators (KPIs) in Power View

You can add key performance indicators (KPIs) (key performance indicator (KPI): A quantifiable measure for gauging business objectives. The KPI gauges the performance of the value, defined by a base measure, against a target value, also defined by a measure or by an absolute value.) to your Power View report to show progress toward goals.

Power View table with KPIs showing flight delays

Use a KPI in Power View

If the data model has KPIs, they’re easy to spot in the Field List. They have this icon KPI icon in Power View Field List next to them. You can use them in Power View sheets in Excel and in Power View in SharePoint.

  1. In Power View in the Field List, click the arrow next to the KPI.

It expands to show the fields in the KPI:

  • Value   : Also called the base value, the calculated field that represents the current value for the item in that row of the table or matrix.
  • Goal: Also called the target value, the value against which the current value is evaluated. This could be a fixed number, some goal all the rows should achieve, or a calculated field, which might have a different goal for each row.
  • Status: The visual indicator of the value. In Power View in Excel, you can edit the KPI, choosing which indicators to use and what values to trigger each indicator.
  1. Check the boxes you want to display.

 Note    SQL Server Analysis Services tabular models can also contain calculated fields and KPIs. You can use those KPIs in Power View in Excel and Power View in SharePoint, too.

In this article


Create a KPI in Power View in Excel

KPIs are based on explicit calculated fields (calculated field: A field in a PivotTable report or PivotChart report that uses a formula you create. Calculated fields can perform calculations by using the contents of other fields in the PivotTable report or PivotChart report.) in the data model. In the Power View Field List, calculated fields have a calculator icon Calculator icon in Power View next to them. If the data model contains calculated fields, then you can use those fields to create KPIs in Power View in Excel. You create KPIs either from Power View in Excel, or by managing the Data Model in the Power Pivot window. Then you can add the KPIs you create to your Power View report.

  1. On a Power View sheet in Excel, on the Power Pivot tab > KPIs > New KPI.

Don’t see a Power Pivot tab in Excel? You may need to enable the Power Pivot add-in.

KPI in PowerPivot

  1. The KPI base field (value) box lists the calculated fields in the data model. Pick the one that contains the value you want to evaluate against a target.
  2. Define a target value. The target value can be:
  • An absolute value – for example, 100.
  • Another calculated field in the model – for example, a field containing sales quotas.
  1. Define the status thresholds and icon styles. You can define either three or five statuses for a KPI, depending on which status threshold and icon style you choose.
  2. Click OK and then Close.
  3. Click OK in the message that says you’ve changed the Data Model.
  4. Now you can use your new KPI in any Power View sheet in this workbook.

Top of Page Top of Page

Modify a KPI in a Power View sheet in Excel

If the data model has KPIs, you can modify them in Power View in Excel, but not in Power View in SharePoint.

  1. On a Power View sheet in Excel, on the Power Pivot tab > KPIs > Manage KPIs.

Don’t see a Power Pivot tab in Excel? You may need to enable the Power Pivot add-in.

  1. Select the KPI you want to edit and click Edit.

KPI in PowerPivot

  1. You can change the base and target values, and status thresholds, and select different icon styles and colors. See Create a KPI in Power View in Excel for details.

Create a calculated field from Power View

You can also create calculated fields from Power View, which you can then use as the basis for creating KPIs.

  1. On a Power View sheet in Excel, on the Power Pivot tab > Calculated Fields > New Calculated Field.

Don’t see a Power Pivot tab in Excel? You may need to enable the Power Pivot add-in.

  1. In the Table Name box, click the table where you want to put the calculated field.

Top of Page Top of Page

More info

Key Performance Indicators (KPIs) in Power Pivot

Create a Calculated Field in Power Pivot

Calculated Fields in Power Pivot

Top of Page Top of Page

 
 
Applies to:
Excel 2013, Power View in Excel 2013, Power View in SharePoint Server 2013