CUBEKPIMEMBER function
Returns a KPI (key performance indicator) value, goal, status or trend defined in an OLAP cube or Data Model.
Syntax
=CUBEKPIMEMBER(connection, kpi_name, kpi_property, [caption])
How to use CUBEKPIMEMBER in Excel, step by step
CUBEKPIMEMBER doesn’t run in a web practice sheet because it reads a data model (Power Pivot / OLAP) connection. Follow these steps in Excel itself.
You’ll need
- A Data Model with a KPI (Power Pivot › KPIs › New KPI) or an OLAP cube with KPIs
- Put your data in a table. Click inside your data, press Ctrl+T, tick “My table has headers” and click OK. Give it a clear name on the Table Design tab (for example Sales).
- Add the table to the Data Model. Go to Insert › PivotTable › From Table/Range, tick “Add this data to the Data Model” and click OK. Excel now has a connection called ThisWorkbookDataModel.
- Build a quick PivotTable first. Drag a field (for example Region) to Rows and a number (for example Amount) to Values. This creates the measure the CUBE functions will read, such as [Measures].[Sum of Amount].
- Let Excel write the formulas for you. Click the PivotTable, then PivotTable Analyze › OLAP Tools › Convert to Formulas. Every cell becomes a CUBEMEMBER or CUBEVALUE formula you can copy and edit.
- Create a KPI. Open Power Pivot › Manage, click a measure, then Create KPI. Set a target value and status thresholds.
- Reference the KPI. Type =CUBEKPIMEMBER("ThisWorkbookDataModel","Sales KPI",1) for the value (2 = goal, 3 = status, 4 = trend).
- Show the number. Wrap it with CUBEVALUE: =CUBEVALUE("ThisWorkbookDataModel",B1).
Example
=CUBEVALUE("ThisWorkbookDataModel",CUBEKPIMEMBER("ThisWorkbookDataModel","Sales KPI",3))
Result: 1 (on target), 0 or -1
kpi_property: 1 value, 2 goal, 3 status, 4 trend, 5 weight, 6 current time member.