An Intro to Excel Cube Formulas for Power Pivot

Page Image - An Intro To Excel Cube Formulas for Power Pivot

In an earlier post I wrote about the advantages of using the Data Model in Powerpivot. Not only does it save you time by not having to juggle with lookup formulas. It also comes with significant file size savings. That all sounds great. Yet for many users, the only way to work with data in the Data Model, is by using a Pivot Table. A Pivot Table unfortunately does not always offer the flexibility spreadsheet developers need. This could prevent Excel Pro’s from using the Data Model. Well, Cube Formulas solve that problem!

What are Cube Formulas?

Data residing in Power Pivot’s Data Model, is not directly visible in the worksheet. That means that there are no cells available on the worksheet for traditional Excel formulas to reference. And so traditional Excel formulas can’t retrieve the Data from the Data Model. Yet Cube Formulas in Excel are formulas that allow users to retrieve data from certain kinds of sources. Cube formulas can interact with the Data Model in Power Pivot.

Now you may say: “Hey I’ve invested all my time in learning DAX to do calculations on the Data Model. How is it that Cube formulas retrieve data from the Data Model in the Excel Worksheet?

There’s no need to worry. In fact, there’s a beautiful collaboration between DAX (measures) and Cube formulas. The DAX language is a necessary component to perform calculations on your dataset. The definition of a measure resides in a so called Measure and is saved as part of the Data Model.

Cube formulas on the other hand reference members and get values from the data cube. A measure defined in DAX, is one of the possible members that cube formulas can reference. You could say that Cube formulas are the portal between the Data Model and the Excel Worksheet. It is through this portal that the Excel user gains access to the Data Model data. Cube Formulas will never replace the DAX formula language. They instead depend on the measures defined in DAX. Since I don’t want to bore you by too much theory, let’s finally get our hands dirty.

How to use Cube Formulas

The easiest way to get started with Cube formulas is by using a Pivot Table that uses the Data Model as its Data Source.

Data Setup

Imagine you have the following dataset.

Excel - 1. Intro to Cube Formulas - Data Model

You can find a Product Sales table that’s connected to a Gender table and a Calendar table. The tables are part of Excel’s Data Model. The tables within the Data Model are connected as illustrated below.

The table Product Sales contains a measure with the DAX formula:

Total Sales = SUM( ‘Product Sales'[Sales])

Create a PivotTable using the Data Model

Knowing the data setup, you can now make a pivot showing the Total Sales per Day Name of the week.

  • Click the tab Insert -> Pivot Table
  • Use this Workbook’s Data Model -> OK
  • Add Day Name to the rows, and the measure Total Sales as Value
Excel - 3. Intro to Cube Formulas - Create Pivot Table
Notice: the above Pivot Makes use of the Data Model. The used tables (shown in bold font) are preceded by a Table Icon with a yellow database icon in the lower right corner. This yellow symbol indicates the table is part of PowerPivot’s Data Model.

Convert To Formulas

The Pivot Table shows the Total Sales categorized by Day Name. Up to now there’s nothing special about this setup. But from here we can transform the displayed values to Cube formulas. So how do you get from a Pivot Table to Cube formulas?

Excel - 4. Intro to Cube Formulas - Convert To Formulas
  • Make sure your cursor is within the Pivot Table
  • Click on the the contextual tab called Analyze. (Note: this tab only appears when you cursor is within the Pivot Table)
  • Click OLAP Tools -> select Convert to Formulas

The displayed values in the Pivot Table transform from a Pivot Table to a Cube Formulas. And the output is the same.

CUBEMEMBER and CUBEVALUE

Take a moment to examine the formulas. The coloured cells on the right contain similar formulas as the coloured cells on the left. The newly created cube formulas are CUBEMEMBER() and CUBEVALUE().

  • The orange marked cell is a CUBEMEMBER formula that contains a cell reference to the DAX Measure called Total Sales.
  • The green marked cells are CUBEMEMBER formulas that contain references to a single member of the data model. In this case the column Day Name from the Calendar table filtered on Friday.
  • The blue marked cell is a CUBEMEMBER formula that also references the column Day Name in the Calendar table. Yet it references all the values, instead of filtering a single day.
  • The numbers are CUBEVALUE formulas that in this example reference two CUBEMEMBER formulas. Notice that the CUBEVALUE formula is the same for each value. It always references the Total Sales Measure, and the cube member to the left of it, referencing the day of the week. Without adjustments, the CUBEVALUE formula can easily be copied down.

CUBEMEMBER() and CUBEVALUE()

The functions CUBEMEMBER() and CUBEVALUE() play a central role in retrieving data from the Data Model. Other Cube formulas exist, yet these two are the most important ones to master. So what’s the purpose of these Cube formulas and how do they work?

CUBEMEMBER()

The CUBEMEMBER function returns a member of the cube. It determines what part of the data cube your CUBEVALUE formula returns. The formula verifies whether the specified member exists in the data model/cube and if it does it will return this member. If the value does not exist, the formula will return #N/A.

The syntax of a CUBEMEMBER formula consists of 3 arguments:
CUBEMEMBER( connection, member_expression, caption)

  1. The first argument is the connection. This refers to the name of your Data Model. Excel automatically creates it, and generally for me this is “ThisWorkbookDataModel”.
  2. The Member_Expression comes second. This argument either slices the data cube through certain members, or indicates a DAX Measure.
  3. The third argument Caption is optional. If you want your CUBEMEMBER argument to show up with a user friendly name, this is where to fill this in. This argument is very flexible and can contain static text, cell references or formulas.

Some common Cube members are:
Column References (e.g. Day Name or Year in a Calendar Table)
Column References filtered on a single value (e.g. the value Friday in column Day Name in the Calendar Table)
Measures (e.g. Total Sales in the Product Sales table).

Examples:

Excel - 6. Intro to Cube Formulas - CUBEMEMBER Examples

CUBEVALUE()

The CUBEVALUE function combines the CUBEMEMBER formulas it references. Then it returns an aggregated value. You can see it as an instruction on how to retrieve data from the Data Model. Its value depends on two elements. First of all it depends on CUBEMEMBER formulas that slice the Data Model down to specified members. It’s like filtering down your data set. And second it depends on the DAX Measure that indicates what calculation it should perform. At the intersection of the referenced CUBEMEMBERS, the CUBEVALUE formula performs the DAX Measure. All the Cube Members functoin as a filter with an AND condition.

The CUBEVALUE formula syntax is:
CUBEVALUE(connection, member_expression1, member_expression2, …)

1. The Connection refers to the Data Model Name.
2. Member Expressions are references to CUBEMEMBER formulas. Member expressions that come after the first one are optional. You can optionally add more of these as you require.

Conclusion

By themselves, the CUBEVALUE and CUBEMEMBER formulas are not very useful. A single CUBEMEMBER formula can show you a Cube member. And a single CUBEVALUE formula won’t return any value. It is their synergy when working together that is invaluable. By leveraging the Data Model, knowing a little DAX and having Cube Formulas as a tool, you can make incredibly powerful Excel reports.

Rick de Groot

Rick is the founder and editor of Excel Gorilla. He believes learning is one of the great pleasures in life and wants to share his knowledge to help you improve your skills. Learn more about him here, connect with him on Twitter, Facebook and LinkedIn and subscribe to his YouTube Channel.

3 Responses

  1. I haven’t finished reading but I wanted to say this is AWESOME, so far! I think this is my solution to creating better dashboards!

    • These formulas are amazing for dashboards. My 2 cents, use as little cube formula’s as possible.

      You could in theory make a dasboard defining all variables in DAX. Like Sales CY (Current Year), Sales LY (Last Year), and have a seperate measure for the difference between these 2. If you already have the Sales CY and Sales TY, I recommend using a normal excel formula to calculate the YoY difference %.

      The reason for this is that each Cube Formula queries the data model, and will use resources. Yet normal excel formulas can calculate with the results of cube formula too. This has far improved the speed of my reports. So less is more for performance reasons!

  2. You state early in this article that ‘… there’s a beautiful collaboration between DAX (measures) and Cube formulas’. I think this is a misleading statement, whilst Cube formulas can access DAX measures, they can also access implicit, non-DAX, measures. Cube formulas are MDX queries, not DAX, it is possible to add MDX expressions into a CUBE formula to do things you couldn’t do otherwise. Cube formulas existed in Excel 2007, before DAX even existed.

    I would also suggest that to build useful dashboards you need to deploy CUBESET, CUBESETCOUNT and CUBERANKEDMEMBER to get dynamic displays.

Leave a Reply

Your email address will not be published. Required fields are marked *

Post comment