Ravindra BagaleCourses & study guides मराठी Track your progress

24. Power Pivot and the Data Model

24.9 PivotTables from the Data Model, and CUBE Functions

By Last updated:

Example first. A PivotTable from the Data Model shows City in rows and Month in columns: Pune Oct ₹540, Nov ₹400. For a dashboard with a fixed layout, Convert to Formulas turns each value into a CUBEVALUE formula you can move anywhere. =CUBEVALUE("ThisWorkbookDataModel","[Measures].[Total Sales]","[Stores].[City].&[Pune]") returns ₹940.

PivotTable from the model and CUBEVALUE — in 30 seconds

What: Insert › PivotTable › From Data Model builds a PivotTable on all related tables at once. CUBEVALUE reads one number from the model with a formula, using the same measure and members.

Why: The PivotTable is fastest for exploring. CUBE formulas let you place model numbers in any cell of a designed report, and they still refresh with the model.

Tiny example: =CUBEVALUE("ThisWorkbookDataModel","[Measures].[Total Sales]","[Stores].[City].&[Pune]") → ₹940. Change "Pune" to "Nashik" → ₹670.

First look at the data (before → after):

PivotTable from the model and CUBEVALUE: before and after tables, fictional Blinkit Maharashtra data

Expected result — Report cells with CUBEVALUE (free layout)

Cell Formula (short) Result (₹)
B3 CUBEVALUE(…Total Sales…, City Pune) 940
B4 CUBEVALUE(…Total Sales…, City Nashik) 670
C4 CUBEVALUE(…Total Sales…, City Nashik, Month Nov) 490
B6 CUBEVALUE(…Total Sales…) 2,315
Before table and formula as text

Before — PivotTable from Data Model: Total Sales by City and Month

City Oct (₹) Nov (₹) Total (₹)
Pune 540 400 940
Nashik 180 490 670
Nagpur 195 160 355
Solapur 90 260 350
Grand Total 1,005 1,310 2,315

Formula / click path

Insert › PivotTable › From Data Model
PivotTable Analyze › Calculations › OLAP Tools › Convert to Formulas
=CUBEVALUE("ThisWorkbookDataModel","[Measures].[Total Sales]","[Stores].[City].&[Pune]")
=CUBEVALUE("ThisWorkbookDataModel","[Measures].[Total Sales]","[Stores].[City].&[Nashik]","[Calendar].[Month].&[Nov]")

Do it step by step:

  1. Click Insert › PivotTable › From Data Model and choose New Worksheet.
  2. In the PivotTable Fields list click All to see every table; put Stores[City] in Rows, Calendar[Month] in Columns and Total Sales in Values.
  3. Click inside the PivotTable, then PivotTable Analyze › Calculations › OLAP Tools › Convert to Formulas.
  4. Click any value cell: the formula bar shows a CUBEVALUE formula.
  5. Cut and paste those cells into your own report layout; they keep working.
  6. Change a number on the Orders sheet and click Data › Refresh All: the CUBE cells update.

Other CUBE functions you will meet

Function What it returns Example
CUBEMEMBER One member (a label) from the model =CUBEMEMBER("ThisWorkbookDataModel","[Stores].[City].&[Pune]") shows Pune
CUBEVALUE A number for a measure and members =CUBEVALUE("ThisWorkbookDataModel","[Measures].[Total Sales]",A3) where A3 holds a CUBEMEMBER
CUBESET + CUBERANKEDMEMBER A list, for top-N reports Top city by Total Sales

Tip for typing: after =CUBEVALUE(" Excel offers ThisWorkbookDataModel, then measures and members in a list. Pick from the list instead of typing names by hand.

Practice task (try it before you open the answer):

  1. Build the City × Month PivotTable from the Data Model.
  2. Convert it to formulas.
  3. Write a CUBEVALUE for Nagpur in November.
Answer

=CUBEVALUE("ThisWorkbookDataModel","[Measures].[Total Sales]","[Stores].[City].&[Nagpur]","[Calendar].[Month].&[Nov]") returns ₹160. Nagpur for both months is ₹355.

Ravindra Bagale's Tip

Convert to Formulas only when the layout is final. After converting, new cities or months do not appear by themselves; a PivotTable would show them automatically.