CUBEKPIMEMBER in Excel: Select KPI Value, Goal and Status Properties
Choose a cube KPI component, distinguish its displayed label from a numeric result, and use CUBEVALUE for calculations under the documented provider restriction.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
CUBEKPIMEMBER selects a property of a key performance indicator defined in a cube. A KPI can bundle an actual value, a target and additional status or trend logic. The function does not create that KPI or decide its business meaning. The model must already define the KPI and the requested component.
Provider prerequisite: Microsoft’s dedicated reference supports this function only for workbooks connected to Microsoft SQL Server 2005 Analysis Services or a later data source. That is a source requirement, not the Excel function’s introduction date. An arbitrary workbook connection is not enough. Official prerequisite
Syntax and selectors
=CUBEKPIMEMBER(connection, kpi_name, kpi_property, [caption])
The connection, KPI name and property selector are required. An optional caption changes the displayed text. The documented selectors are:
| Selector | Component | Intended meaning |
|---|---|---|
| 1 | KPIValue | Actual value |
| 2 | KPIGoal | Target |
| 3 | KPIStatus | State at a point in time |
| 4 | KPITrend | Behavior over time |
| 5 | KPIWeight | Relative importance |
| 6 | KPICurrentTimeMember | Time context |
Selecting 1 without a custom caption displays the KPI name. For calculation, pass the KPI member to CUBEVALUE rather than assuming the label cell is a number. Microsoft’s selector and calculation rules
Worked example: separate actual and goal
Assume an already authorized connection named TrainingCube meets the documented Analysis Services prerequisite. Define a fictional KPI named UnitsTarget, with valid actual and goal components. In B2 and C2, the proposed formulas are:
=CUBEKPIMEMBER("TrainingCube","UnitsTarget",1,"Units actual")
=CUBEKPIMEMBER("TrainingCube","UnitsTarget",2,"Units goal")
If the KPI resolves, these cells would display Units actual and Units goal, respectively, while referring to different KPI components. Neither caption is the KPI’s numerical value. The names are illustrative and do not describe a real connected model.
To obtain values for arithmetic, use separate cells B3 and C3:
=CUBEVALUE("TrainingCube",B2)
=CUBEVALUE("TrainingCube",C2)
Now explicitly stipulate that, under the same model time and filter context, the toy actual is 80 and the toy goal is 100. Only under those additional conditions would B3 and C3 represent 80 and 100. The illustrative ratio B3/C3 would then be 0.8, or 80%. Those figures are declared teaching inputs, not retrieved performance data.
This separation makes the report auditable: one row states which KPI component is selected; another contains its value. Replacing B2 with the text Units actual would preserve its appearance while discarding the defined KPI-member relationship. A report copied as values should be treated as a static snapshot, not an equivalent live formula chain.
Advanced use: preserve the KPI’s context
A KPI’s status, trend and weight can encode model rules. Do not assume status is always a traffic-light number with universal thresholds, or that trend is simply current actual minus goal. Selector 6 identifies time context, so review what period the KPI means before comparing it with a separately filtered measure.
For a reusable selector, place the connection name in A1, KPI name in A2 and the integer component code in A3:
=CUBEKPIMEMBER($A$1,$A$2,A3,"Selected KPI component")
In a report design, accompany A3 with a human-readable selector label and restrict its intended values to the six documented choices. A generic caption is convenient while prototyping, but a production heading should identify whether the cell represents an actual, target, status or other component. Changing only the selector without updating that heading can mislead readers even if the formula remains valid.
Before computing achievement, decide whether a zero or missing goal has a meaningful policy. A denominator guard using IF is a presentation decision; it should not invent a target. If the actual and goal relate to different periods or units, a division that succeeds mathematically can still be a bad comparison. Document units and period together with the KPI name.
Errors and maintenance
An invalid connection or unavailable/erroring OLAP server has documented #NAME? behavior. An invalid KPI name or property selector produces #N/A. Session-based dependencies may fail after their originating PivotTable changes. Pending retrieval can display #GETTING_DATA…. Microsoft’s failure notes
Diagnose provider compatibility first, then the stored connection, KPI identity and component selector. Only after those resolve should you investigate the numeric CUBEVALUE stage. IFERROR can label an unavailable result, but keep the original error available during testing so an outage does not appear to be a zero KPI. Use FORMULATEXT when a review worksheet needs to expose the selected property and caption explicitly.
Availability and evidence
Microsoft’s dedicated reference, checked October 18, 2026, lists Excel for Microsoft 365 and Excel 2024/2021 on Windows and Mac, Excel 2019/2016, iPad, iPhone, Android tablets/phones and Excel Web App. Those labels describe the article’s scope; they do not prove that every listed client can create or refresh every OLAP connection. Check the actual workbook, provider, permissions and client before deploying a report. No subscription entitlement beyond those product labels was established here.
First introduction and last functioning build are unknown. No Excel installation, workbook connection or server query was performed. Every model and output in this guide is hypothetical or a documented conditional expectation; no native result or screenshot is claimed.