CUBEKPIMEMBER Function

Excel Functions › Cube

Excel 2007+ Data Model / OLAP

The Excel CUBEKPIMEMBER function returns a Key Performance Indicator property — the value, goal, status, or trend of a KPI defined in an Analysis Services cube or Power Pivot model — so dashboards can readofficial KPIs straight from the model.

Quick answer: the current value of the Revenue KPI:
=CUBEKPIMEMBER("ThisWorkbookDataModel", "Revenue", 1) // 1 = KPIValue
Wrap it in CUBEVALUE to get the number itself.

Syntax

=CUBEKPIMEMBER(connection, kpi_name, kpi_property, )
ArgumentDescription
connectionRequired"ThisWorkbookDataModel" or an OLAP connection.
kpi_nameRequiredKPI name as text, exactly as defined in the model.
kpi_propertyRequired1 KPIValue · 2 KPIGoal · 3 KPIStatus · 4 KPITrend · 5 KPIWeight · 6 KPICurrentTimeMember.
captionOptionalDisplay text override.

What you need first: cube functions only work against a Power Pivot Data Model (or an external OLAP cube). Add your table to the model — check “Add this data to the Data Model” when inserting a PivotTable, or use the Power Pivot tab — then reference it with the connection name "ThisWorkbookDataModel" and MDX-style member strings like "[Measures].[Total Sales]" or "[Products].[Category].[Displays]".

How to use it

KPI scorecard row — value vs goal vs status:

=CUBEVALUE("ThisWorkbookDataModel", CUBEKPIMEMBER("ThisWorkbookDataModel","Revenue",1)) // actual
=CUBEVALUE("ThisWorkbookDataModel", CUBEKPIMEMBER("ThisWorkbookDataModel","Revenue",2)) // goal

KPIs are defined in Power Pivot (Home → KPIs) on top of a base measure with a target and thresholds.

Try it: interactive demo

Live demo

Pick a KPI property constant and see what the formula asks for.

Result:

Errors & common pitfalls

#NAME? — bad connection name. The first argument must exactly match a workbook connection; for the built-in Data Model it is "ThisWorkbookDataModel", quotes included.

#N/A — invalid member expression. Table, column, or item names in the MDX string don’t match the model. Check spelling and bracket every level: [Table].[Column].[Item].

“#GETTING_DATA” flashes in cells. Normal — Excel retrieves cube results asynchronously. It resolves when the query finishes.

Performance: hundreds of separate CUBEVALUE calls query the model one by one. Pull shared members into CUBEMEMBER cells once and reference those cells in your CUBEVALUEs.

Practice workbook

📊
Download the free CUBEKPIMEMBER practice workbook
Every example on this page, ready to open in Excel — plus practice challenges with answers on a separate tab. No sign-up required.

Frequently asked questions

Where do KPIs come from?
They're defined in the model: in Power Pivot select a measure → Create KPI, set a target and status thresholds. Analysis Services cubes ship them pre-built.
CUBEKPIMEMBER returns a member, not a number — why?
Like CUBEMEMBER, it returns a reference into the cube. Wrap it in CUBEVALUE to pull the actual number.
What do KPIStatus values mean?
Typically -1 (below threshold), 0 (acceptable), 1 (on/above target) — exact semantics come from the KPI's status expression in the model.
Do I need this if I just have measures?
No — plain measures only need CUBEVALUE. CUBEKPIMEMBER matters once goals/status/trend live in the model as proper KPIs.

Master functions like this in one day

This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related functions: CUBEVALUE · CUBEMEMBER · CUBESET · GETPIVOTDATA · GROUPBY