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
| Argument | Required? | What it does |
|---|---|---|
| connection | Yes | The 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_name | Yes | The KPI's name in quotes. For a Data Model KPI, that's the name of the measure the KPI is built on. |
| kpi_property | Yes | 1 value, 2 goal, 3 status (usually -1, 0 or 1), 4 trend, 5 weight, 6 current time member. |
| caption | Optional | Text 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))
| A | B | |
|---|---|---|
| 1 | Part | Value |
| 2 | Actual | 1,193,850 |
| 3 | Goal | 1,250,000 |
| 4 | Status | 0 |
- B3 uses kpi_property 2 (goal).
- 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
- Create KPIs in Power Pivot: right-click a measure in the Calculation Area › Create KPI.
- Cube functions talk to a live Data Model or Analysis Services cube, so they can't run in our practice Sandbox.
- Related functions: CUBEVALUE, CUBEMEMBER, CUBESET, CUBERANKEDMEMBER, CUBESETCOUNT, CUBEMEMBERPROPERTY, CUBEKPIMEMBER, GETPIVOTDATA.

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.