Power Pivot KPIs in Excel: Create, Edit, and Switch Views

Charts & pivot tables · Updated November 21, 2026

To switch what a Power Pivot KPI shows in an Excel PivotTable, expand the KPI in the PivotTable Fields pane and add or remove its Value, Goal, or Status component. To change the KPI’s thresholds or icons, edit the KPI in the Power Pivot window; a KPI is not a single on/off switch, and its status is calculated from a measure and its target.

What a Power Pivot KPI is

A key performance indicator (KPI) in Power Pivot is a model measure evaluated against a target. It can also assign a status—often shown as a traffic-light icon—based on how the measure compares with that target. For example, a sales measure can be assessed against a sales goal, and the KPI can label performance as below, near, or at or above the desired level.

The KPI is part of the workbook’s Data Model. It is not the same thing as an Excel cell formula with a colored icon, nor is it merely a chart or conditional-formatting rule. The model holds the measure, target and status criteria, while a PivotTable can display one or more of those components. That distinction matters when someone says “switch the KPI”: they might mean changing the displayed component, changing the KPI definition, or changing which business metric is being analyzed.

Before you create or edit one

These instructions are for Excel for Microsoft 365 on Windows. Power Pivot authoring is a Windows desktop feature, and availability can depend on the Excel edition and organization license. If the Power Pivot tab is missing, first check whether the add-in is available and enabled: choose File > Options > Add-ins. At the bottom, select COM Add-ins in the Manage box, choose Go, and look for Microsoft Power Pivot for Excel. If it is listed, select it and choose OK.

Excel for the web does not provide the full Power Pivot window for creating and editing model KPIs. Excel for Mac also does not provide the same Power Pivot authoring experience as Excel for Windows. A workbook containing a Data Model may have limited viewing or refresh behavior in other environments, but do not assume that a KPI can be authored or edited there. For model changes, use a supported Windows desktop version of Excel.

A KPI also needs a suitable base measure. Measures are calculations in the Data Model, commonly created with Data Analysis Expressions (DAX). A measure should represent the result you intend to assess, such as total revenue, units shipped, or on-time delivery rate. The target must be meaningful for that measure and for the filters users will apply.

Create a KPI in Power Pivot

  1. In Excel, open the workbook containing the Data Model and choose Power Pivot > Manage. This opens the separate Power Pivot window.
  2. Locate the table containing the measure. If you cannot see the calculation area at the bottom of the table window, use the calculation-area control on the Home tab to show it.
  3. Select the measure you want to evaluate. You can also right-click the measure in the calculation area and choose Create KPI, when that command is available.
  4. In the KPI dialog, identify the target. Depending on the dialog options, the target can be a numeric value or another measure. Use a target expressed on the same basis as the KPI measure.
  5. Set the status thresholds and choose an icon style. The thresholds define the boundaries between status categories; the icons communicate those categories visually.
  6. Confirm the dialog, return to Excel, and create or select a PivotTable based on the workbook’s Data Model. In the field list, expand the KPI to see its available components.

Power Pivot’s labels and dialog presentation can vary somewhat across Excel builds, but the underlying idea is the same: choose a base measure, specify a target, then define how the actual result maps to status. If the KPI command is unavailable, confirm that the selected item is a measure in the calculation area, rather than a column heading or an ordinary worksheet cell.

Switch between Value, Goal, and Status

In the PivotTable Fields pane, a KPI is typically shown with expandable components. Drag the component you need into the appropriate PivotTable area. Value and Goal can be displayed as numbers; Status displays the KPI result as an icon or status indicator. Remove a component from the field layout to stop displaying it. This changes the PivotTable view, not the KPI’s definition in the Data Model.

  • Value: the current result of the base measure, such as actual sales for the selected month.
  • Goal: the target value used for comparison, if the KPI has a target that can be displayed.
  • Status: the category determined by comparing the value with the target and applying the KPI’s thresholds.

For example, imagine a monthly sales measure with a goal of $100,000. A PivotTable can show actual sales and the goal as separate numbers, show the status icon, or include all three for context. Dragging Status into the PivotTable does not replace the actual-sales value; it adds a distinct KPI component. If the PivotTable has many rows, placing Status beside the relevant measure can make the comparison easier to scan.

To change the KPI itself rather than its display, return to Power Pivot > Manage, locate the KPI measure in the calculation area, and use its context menu to edit the KPI. Adjust the target, thresholds, or icon set as needed, then review the PivotTable. If the updated result does not appear immediately, refresh the PivotTable or workbook data as appropriate.

Choose useful targets and thresholds

Thresholds should reflect what “good” means for the particular metric. A target of $100,000 with a lower boundary at 90% of target could treat results below $90,000 as the lowest status, while results between $90,000 and the target occupy the middle band. Results meeting or exceeding the target receive the higher status. The exact threshold values and interpretation depend on the settings selected in the KPI dialog, so check the displayed boundaries rather than assuming an icon’s meaning.

For a measure where lower is better, such as defects or late deliveries, the intuitive direction may be reversed: a smaller value can be favorable. Do not choose a traffic-light scheme based only on its colors. Confirm how the KPI status behaves for both a value below target and one above target. Test representative cases, including values exactly on a boundary, before distributing the workbook.

Targets also need to make sense under filtering. A single annual target may be misleading when the PivotTable is grouped by month, product, or region. Consider whether users will compare each row with the same target, whether the target should vary by period or category, and whether the target measure responds correctly to the same filters as the actual measure. A status icon without a visible value or goal can be ambiguous, so include enough context for readers to understand what it means.

Switching to a different metric is different

Power Pivot does not provide a universal “switch KPI” control that automatically rotates among unrelated measures. A KPI is tied to its base measure and target definition. If you want to assess a different metric, create or select the appropriate measure and define a KPI for it, or build a separate report interaction that changes which measure is shown. Simply removing Status from the PivotTable and adding Value changes the displayed component; it does not change sales into profit or change the target.

For a report with several KPIs, one straightforward design is to place each KPI’s value, goal, and status in a clearly labeled area of the PivotTable or dashboard. Another is to let users choose among predefined views using separate PivotTables or report pages. More advanced dynamic measure selection requires deliberate Data Model design; it is not achieved by toggling the built-in KPI status icon. Keep the target logic visible and documented so a reader can tell which measure is being evaluated.

Common problems and how to resolve them

  • The KPI does not appear in the field list: Confirm that you created a KPI from a measure in the Data Model, not from a worksheet cell or an ordinary table column. Reopen the PivotTable Fields pane and expand the measure or KPI entry.
  • Create KPI is unavailable: Select a valid measure in Power Pivot’s calculation area. A column or a table name is not interchangeable with a measure. Also check whether the workbook is using the Data Model and whether the Power Pivot add-in is enabled.
  • The icons look wrong: Review the target, threshold boundaries, icon style, and direction of the metric. A KPI cannot know that fewer defects are better unless its status settings express that intended relationship.
  • The status is blank or unexpected: Check whether the target evaluates to a usable value in the current filter context and whether the base measure returns a result for the row. Missing data, a blank target, or filters that exclude relevant records can affect the comparison.
  • The displayed result seems stale: Refresh the PivotTable or the underlying Data Model after changing source data or model definitions. Also verify that the PivotTable is connected to the expected workbook model.

When troubleshooting, compare the numeric value and goal before interpreting the icon. That separates a display problem from a calculation or filter-context problem. If the numbers are not what you expect, investigate the measure and the PivotTable filters first; changing the icon style will not correct the underlying calculation.

Frequently Asked Questions

How do I show only the KPI status icon?
Expand the KPI in the PivotTable Fields pane and drag Status into the PivotTable layout. Remove Value or Goal from the layout if you do not want those components displayed. Keeping a label or nearby explanation can help readers interpret the icon.

Can I change a KPI’s red, yellow, and green thresholds?
Yes. Open Power Pivot > Manage, select the KPI measure, and edit the KPI settings. Adjust the status boundaries and icon style in the KPI dialog, then review the results in the PivotTable.

Does removing Status delete the KPI?
No. Removing Status from a PivotTable changes only that PivotTable’s displayed fields. The KPI remains in the Data Model and can be added to the layout again.

Can one KPI measure use different targets in different PivotTable rows?
It can depend on how the target is defined and how it responds to the model’s filter context. A target measure may vary by category or period if its calculation is designed to do so. Check the target values in the actual PivotTable context before relying on the statuses.

Can I create or edit Power Pivot KPIs in Excel for Mac or Excel for the web?
Those versions do not offer the same Power Pivot KPI-authoring tools as Excel for Microsoft 365 on Windows. Use a supported Windows desktop version to create or edit the KPI, and verify what viewing and refresh capabilities are available in the other version you use.

Make the switch meaningful to readers

For most users, “switching” a Power Pivot KPI means choosing which of its Value, Goal, and Status components appears in a PivotTable. Use the field list for that display change; edit the KPI in Power Pivot when the target, thresholds, or symbols need to change. If the goal is to switch among different business measures, treat that as a separate report-design task. Keeping actual values, targets, and status rules clear makes the resulting KPI more useful—and less likely to be misread.

Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.