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
CUBERANKEDMEMBER formula to complete the pack
The last formula that I want to touch upon is CUBERANKEDMEMBER, which basically ranks your cube dimension items based on a certain criteria, such as top 10 or bottom 5, etc… To use that function you also need to refer to CUBESET formula in its syntax.
Example, we want to see our Manufacturers from Adventure Works database listed alphabetically. First we create a CUBESET function to contain all the Manufacturers (or part of them if thats needed.)
=CUBESET("ThisWorkbookDataModel","-{[Data Product].[Manufacturer].[All].[A. Datum Corporation],[Data Product].[Manufacturer].[All].[Adventure Works]}", ("A. Datum & Adv Works"))Then we can embed the CUBESET formula into our CUBERANKEDMEMBER to sort the Manufacturers alphabetically. Pay attention that we are intentionally excluding “Adventure Works” Manufacturer in the CUBESET function by providing a minues “-” before the curly bracket “{“. Note that $A$37 is simply a reference to the cell where our CUBESET formula is sitting.
=CUBERANKEDMEMBER("ThisWorkbookDataModel",$A$37,ROW(A1))The results will give us the first member of the CUBESET, which is “Contoso, Ltd”. If we copy the formula down, then automatically we get 2nd, 3rd, 4th, etc… member. This is the doing of the ROW(A1) function that does count of rows.
The top ten list look as follows:
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!
Another solution for the Blanks issue is to add +0 after the cubevalue formula so it will return an error instead of blank, then use iferror to turn into a zero. Like this: Iferror(Cubevalue ()+0).
Thats a good trick! thank you
You could also wrap an n() function around the cubevalue formula, which is even easier. By the way, there’s an Excel add-in that converts pivots to cubevalues and automatically applies formatting and groupings. It’s called something like Accelerate Excel.