CUBEKPIMEMBER Function

Returns a key performance indicator (KPI) — its value, goal, status or trend.

Category

Cube

Available in

Excel 2007+ for Windows, Microsoft 365

Returns

A KPI property (shows the KPI name)

Overview

A KPI is a measure with a target attached, such as monthly sales against a goal. CUBEKPIMEMBER fetches one part of it — the value, the goal, the status or the trend.

The cell shows the KPI's name; wrap it in CUBEVALUE to get the number itself.

Syntax

=CUBEKPIMEMBER(connection, kpi_name, kpi_property, [caption])

Arguments

ArgumentRequired?What it does
connectionYesThe connection name in quotes. For the workbook's own Data Model it's "ThisWorkbookDataModel"; for an Analysis Services cube, use the name shown in Data › Queries & Connections.
kpi_nameYesThe KPI's name in quotes. For a Data Model KPI, that's the name of the measure the KPI is built on.
kpi_propertyYes1 value, 2 goal, 3 status (usually -1, 0 or 1), 4 trend, 5 weight, 6 current time member.
captionOptionalText to show instead of the KPI name.

Examples

Example 1 — Sales against target

Get the KPI's current value and its goal, side by side.

B2fx=CUBEVALUE("ThisWorkbookDataModel",CUBEKPIMEMBER("ThisWorkbookDataModel","Total Sales",1))
AB
1PartValue
2Actual1,193,850
3Goal1,250,000
4Status0
Illustrative values.
  1. B3 uses kpi_property 2 (goal).
  2. B4 uses 3 (status): 1 on track, 0 close, -1 behind — turn it into icons with Conditional Formatting › Icon Sets.

Common mistakes

  • The cell shows a name, not a number. CUBEKPIMEMBER returns the KPI member. Put it inside CUBEVALUE to get the value.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.