SheetSkill

CUBEVALUE function

Returns a total (a measure) from a Power Pivot Data Model or an OLAP cube, filtered by the members you list.

Works in desktop Excel only

Syntax

=CUBEVALUE(connection, [member_expression1], [member_expression2], …)

How to use CUBEVALUE in Excel, step by step

CUBEVALUE 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

  • Excel for Windows or Mac (Microsoft 365, 2021 or 2019)
  • A Data Model or OLAP connection in the workbook
  1. 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).
  2. 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.
  3. 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].
  4. 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.
  5. Write your own CUBEVALUE. In an empty cell type =CUBEVALUE("ThisWorkbookDataModel","[Measures].[Sum of Amount]","[Sales].[Region].&[North]"). Excel suggests names as you type each part.
  6. Point it at member cells. Instead of typing members, refer to cells that hold CUBEMEMBER formulas, e.g. =CUBEVALUE("ThisWorkbookDataModel",$B$1,A5). Copy the formula across a grid to build a report.
  7. Refresh after the data changes. Use Data › Refresh All. CUBE formulas show #GETTING_DATA for a moment while they load.

Example

=CUBEVALUE("ThisWorkbookDataModel","[Measures].[Sum of Amount]","[Sales].[Region].&[North]")

Result: Total amount for the North region, e.g. 48,250

Text in the member expressions must match the model exactly, including square brackets.
A #N/A usually means a member name is misspelt.