# 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.

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

**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?

- 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.

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)**

- 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”. - The
**Member_Expression**comes second. This argument either slices the data cube through certain members, or indicates a DAX Measure. - 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:

### 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.

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!

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.

Hi Bob. Thanks for your elaborate reply on this article. Great addition to also use CUBESETCOUNT, CUBERANKEDMEMBER and CUBESET formulas. I haven’t used the CUBESETCOUNT one before but will look into it and perhaps write about it later. What do you use the CUBESETCOUNT formula for? Fun to share?

In this article I introduced cube formulas. And the CUBEMEMBER and CUBEVALUE formulas are the ones users encounter first. Knowing these, a user can start making some basic reports. Introducing all cube formulas at once may be much for people new to these formulas.

Also, my experience with MDX is limited and I can only imagine there’s much more one can do using MDX and DAX.

As I see it, there is a beautiful collaboration between DAX and CUBE formulas. That doesn’t change CUBE formulas can be used for other things too and have been around a long time. What do you mostly use cube formulas for? Would be be great to read in the comments!

Best regards,

Rick

Riddle me this batman. I converted my power pivot table to OLAP cube formulas. Everything worked great. Then I added next month’s data file (with the 12/6/2019 data).

I wrote out the formula to bring that date in to the column field

=CUBEMEMBER(“ThisWorkbookDataModel”,”[FTE Accruals Data].[Date].&[2019-12-06T00:00:00]”)

And it worked! Good so far.

Then I tried to use cubevalue to bring in the sum of amount for each G/L (like it did for the previous month) and it’s not returning any values for me. It’s just blank.

=CUBEVALUE(“ThisWorkbookDataModel”,$B$2,$F4,K$3)

B2 = =CUBEMEMBER(“ThisWorkbookDataModel”,”[Measures].[Sum of Amount 2]”)

F4 = =CUBEMEMBER(“ThisWorkbookDataModel”,{“[FTE Accruals Data].[Agency or FTE].&[FTE]”,”[FTE Accruals Data].[Business Plan].&”,”[FTE Accruals Data].[Co Code].&”,”[FTE Accruals Data].[Cost Center].&[]”,”[FTE Accruals Data].[GL].&[517987]”})

K3 = =CUBEMEMBER(“ThisWorkbookDataModel”,”[FTE Accruals Data].[Date].&[2019-12-06T00:00:00]”)

It should be returning a value for me. I’m a bit dumbfounded, and have spun my wheels on this one.

If you’d like to see the file I have no problem emailing it to anyone willing to help.

Hey Jason!

I would love to take up the challenge. Could you share it with me through the contact form, or directly through [email protected]?

Look forward to your email!

Rick

Hi,

Great blog! I’m new to these Cube formulas. Everything is working great except, i can’t seem to get my new lines to append. I’m pulling my data from SQL. To start, I selected a small amount of data to build it. Once i had the layout i wanted. I removed my “Where” clause. The row count in my queries went from 1000 rows to 1800. But my worksheet is still only showing the original 1000 rows. How do i make it show all the rows? Is there somewhere i need to extend the range?

Thanks,

Mark