Excel like a finance pro.
Cube function
CUBEVALUE
Returns an aggregated value from an OLAP cube or Power Pivot data model.
When to use it
An aggregated value from a cube or Power Pivot data model for a combination of members. The formula alternative to a pivot table cell.
The shape of it
- Syntax
=CUBEVALUE(connection, [member_expression1], ...)
- Example
=CUBEVALUE("ThisWorkbookDataModel","[Measures].[Total Sales]","[Region].[West]")
Worked examples
Grand total
=CUBEVALUE("ThisWorkbookDataModel","[Measures].[Total Sales]") → 1,250,000
The grand total of a measure.
Filtered
=CUBEVALUE("ThisWorkbookDataModel","[Measures].[Total Sales]","[Region].[Region].[West]") → 425,000
One region.
Report grid
=CUBEVALUE("ThisWorkbookDataModel",$A$1,$A2,B$1) → grid of values
Measure in A1, regions down column A, products across row 1: a free-form report.
Worth knowing
- Works against a Power Pivot data model in the same workbook, no server needed.
- PivotTable Analyze, OLAP Tools, Convert to Formulas turns a pivot into these.
- Reference CUBEMEMBER cells rather than repeating MDX.
- Slicers can be passed as arguments: Slicer_Region.
Where it goes wrong
- #N/A while refreshing or for a member that does not exist.
- #GETTING_DATA appears briefly during calculation.
Related
Learn the moves here — or let Wauvel run them on your numbers.
Meet your AI CFO →One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.