Skip to content

Excel Cube functions explained: Cubevalue, Cubeset, Cubemember

One of my favorite Excel functions to do business modeling are Cube functions. They act as a more efficient replacement for SUMIFS, GETPIVOTDATA functions that serve to sum aggregated results with various filters applied. Cube functions (CUBEVALUE, CUBEMEMBER, CUBESET) will only work if you have a PowerPivot model or live cube connection in your Excel file.

How to use Cube functions

In my experience, when using Cube functions, I prefer to have PowerPivot model because it calculates a lot faster than connecting to live cube. The easiest way to start using Cube functions is to start with a Cube pivot, click anywhere in it, then go to ribbon PivotTable Analyze -> OLAP Tools -> Convert to Formulas

After the pivot table is converted to formulas, it is easy to examine their structure of the formulas and start using them for modeling.

CUBEVALUE Function

The CUBEVALUE function basically sums up the values filtered or sliced by the CUBEMEMBER as in cells A5:A15 in our example below.

The formula consists of Connection Name, which is in our case “ThisWorkbookDataModel” – this is my PowerPivot model. If your model is a live cube, it maybe called differently. In any case, all you have to do to find out your model name is to start typing ” after the parenthesis =CUBEVALUE( and the intelli-sense will propose your model name.

After the model name “ThisWorkbookDataModel”, the CUBEVALUE function is anchored to different cells containing filters. For example, cell $B$1 is not passing any filter context to the function, due to having “All”. However, cell $A5 is telling the formula to only look up “A. Datum Corporation” product. The cell $A$3 is specifying to use Total Cost measure. You can pack any amount of filters you like into the CUBEVALUE formula, as long as they don’t contradict each other.

CUBEMEMBER Function

The CUBEMEMBER formula basically stores the filter context that could be applied to CUBEVALUE formula. It can be either written in a separate cell or directly inside the CUBEVALUE formula.

From our example above, the filter cells such as A$5, showing “A. Datum Corporation” is not a simple text cell, but is a CUBEMEMBER function. It’s content is:

=CUBEMEMBER("ThisWorkbookDataModel","[Data Product].[Manufacturer].&[A. Datum Corporation]")
or using All instead of &
=CUBEMEMBER("ThisWorkbookDataModel","[Data Product].[Manufacturer].[All].[A. Datum Corporation]")

CUBEMEMBER function basically states the hierarchy of the filter where it is located, which in our case is located in the dimension table of Data Product.Manufacturer.

CUBESET Function

Sometimes we want to create a filter that selects multiple items like “A. Datum Corporation” and “Adventure Works”. For that reason, we need to use CUBESET, which will look as follows:

=CUBESET("ThisWorkbookDataModel","{[Data Product].[Manufacturer].[All].[A. Datum Corporation],[Data Product].[Manufacturer].[All].[Adventure Works]}", ("A. Datum & Adv Works"))

Note the {} brackets around the multiple items in the CUBESET. You cannot create a CUBESET with items from different tables. In our case they all need to come from Data Product.Manufacturer. The last part of the formula is where I give any descriptive name to the set “A. Datum & Adv Works”.

Reverse CUBESET (all except filter)

What if I want to have a reverse filter (all except the selection) to the selection I just made with the CUBESET? Easy enough, just put a negative sign before the brackets. It will give us then the Total Cost of all products except A. Datum Corporation and Adventure Works. This is where CUBESET excels compared to the classic SUMIFS and GETPIVOTDATA formulas.

=CUBESET("ThisWorkbookDataModel","-{[Data Product].[Manufacturer].[All].[A. Datum Corporation],[Data Product].[Manufacturer].[All].[Adventure Works]}", ("A. Datum & Adv Works"))

Note that it is also possible to combine CUBEVALUE with CUBEMEMBER or CUBESET functions into 1 big formula instead of having them anchored in different cells:

When creating models, often we need to reference formulas to various cells controlled by drop-downs or other formulas. We can easily also anchor our CUBEMEMBER/CUBESET functions to those cells using “& notation to open and &” close the anchor.

Example below: We want to link the function CUBEMEMBER to yellow cell $C$3

Mathematical operations with CUBEVALUE functions gives errors with blanks

I noticed a drawback in Excel when building models using CUBE functions, is that it throws out and error #VALUE when any kind of mathematical operation (addition, subtraction, multiplication, division) is performed on a CUBEVALUE that has NULL or blank entry.

For example, we want to sum up a list of product cost, where one of the products doesn’t exist in the model. In the example below for a cell B6, there is no Total Cost for “Nunavut” company, so if I use simple addition in Excel, it throws out #VALUE in stead of counting it as a 0.

I tried using IFERROR or other functions, but it still is giving an error. The best workaround is to use SUM() around each cell to make it work. see below

CONCLUSION

Cube functions are a great way to link your formulas to the OLAP Cube or PowerPivot model and calculate results. It is very simple to perform is very easy to read compared to SUMIFS. You don’t need to create backend pivots or data tables except having the powerpivot / cube model in the backend.

You can check my other blog post about the comparison between GETPIVOTDATA, SUMIFS, CUBEVALUE here.

I also made a video of Cube functions on my Gangjing channel here. Check out Ganjing World for a better alternative to youtube!

Leave a Reply

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