Excel CUBE Functions

Some might say that Excel CUBE functions are the new PivotTables because they allow us to build reports based on Power Pivot models in a more free-form fashion. Whereas PivotTables are quite restrictive in their layout and their cells cannot be edited. And just like PivotTables, we can use Slicers to filter CUBE functions for fully interactive reports and dashboards.

Watch the Video

Download Workbook

Writing Excel CUBE Functions

When you’re starting out with CUBE functions, you’ll find it easier to automatically generate CUBE formulas by converting an existing PivotTable like the one below containing sales by category and sub-category for each month.

It’s a good idea to set the layout in a Tabular Form so you don’t have redundant headings once you make the conversion.

To convert a PivotTable, select any cell in the PivotTable > then on the PivotTable Tools: Analyze tab > OLAP tools > Convert to Formulas.

Note: If you have fields in the Filters area, then you’ll be prompted to choose to either also convert them or keep them so you can still filter the data. I prefer to use a Slicer for the filtering, so I don’t have any filters on this PivotTable.

This conversion permanently replaces the PivotTable with CUBE formulas.

In the formula bar you can see the values have been replaced with CUBEVAUE formulas:

And the row and column labels have been replaced with CUBEMEMBER formulas:

CUBEVALUE Function

Looking at the CUBEVALUE formulas more closely you can see the first argument is the connection, this is your data model for the workbook, which will always be called ‘ThisWorkbookDataModel’, and then we have a series of member_expression arguments which essentially filter the data.

Member expressions can be other cube functions that evaluate to a set of members or a single member in your model, or they can reference other cells that contain these cube formulas or member values, as we can see with the formula above.

To be clear; Members can be the fields, items, measures, or Slicers in your model.

In the formula above the member expressions are the measure; Sum of Amount, the Sub-category in the row label, the month in the column label and the Slicer for the year.

The Slicer name is entered when the CUBE formulas are cretaed. However, if you need to find the Slicer name, you can right-click and view it in the Slicer Settings. It’s the ‘Name to use in formulas’.

Tip: You can change the Slicer name via the Name Manager if you want.

CUBEMEMBER Function

The row and column label cells contain CUBEMEMBER formulas:

Notice CUBEMEMBER also requires the connection for the first argument, and then it references the Categories table to get the Category, Charity. These items are hard coded, which is not ideal, and we’ll look at how we can automate this in a moment, but first I want to point out the Grand Total CUBEMEMBER formula below:

Here, instead of a hard coded category name, we have [All], which essentially says to include all of the categories in this CUBEMEMBER. Because this formula returns all category names, we can use the last argument for the caption to tell the formula to display ‘Grand Total’ in the cell.  You can change it to display something else if you want.

Now that we’ve converted our PivotTable to cube formulas we can cut and paste them to other locations in our workbook, keeping in mind the cells they reference. And just like any other formula, you can perform further math calculations with them.

Making CUBE Functions Portable

Another option is to nest the CUBEMEMBER values inside the CUBEVALUE formulas to make them more portable.

For example, you could simply copy the CUBEMEMBER formula arguments after “ThisWorkbookDataModel” from the cells the CUBEVALUE formula is referencing creating one big formula like so:

=CUBEVALUE("ThisWorkbookDataModel",
"[Measures].[Sum of Amount]",
"[Categories].[Category].&[Charity]",
"[Data].[Sub-category].&[Donation]",
"[Data].[Date (Month)].&[Jan]",
Slicer_Date__Year)

Now that this cube formula is directly referencing the model, I’m free to move it anywhere in this workbook as it’s no longer dependent on any other cells. The other benefit is this formula is easy to read because all elements are explicitly listed.

Making CUBE Formulas Dynamic

If you’re writing formulas from scratch, it’s a lot of work to create each formula with the category, sub-category and month hard keyed.

Instead, we can make the formulas dynamic by referencing cells containing text values for the row and column labels and even the measure. That is, you don’t have to reference CUBEMEMBER formulas in your CUBEVALUE formulas. This means I can write one formula and then copy and paste them to the cells in the table, which is a super-fast way to write your CUBE formulas and build your reports.

And with all these formulas, I can still use the Slicer to filter the table!

CAUTION: This technique is ok for simple models, like the in this example, however if you’re working with models that have these items listed in multiple tables, it’s better to fully qualify the row and column labels to avoid ambiguous results, like so:

=CUBEVALUE("ThisWorkbookDataModel", $B$6,
"[Data].[Sub-category].["&$C8&"]",
"[Data].[Date (Month)].&["&D$7&"]",
Slicer_Date__Year11)

More CUBE Functions

If you’d like to learn more CUBE functions and Power Pivot, please consider my Power Pivot and DAX course.