Tips & Tricks for Writing CUBEVALUE Formulas - Excel Campus
20

Tips & Tricks for Writing CUBEVALUE Formulas

Imagine it’s a hot summer day and you are enjoying a nice cold drink at your favorite spot.  What’s one thing that will make that drink extra refreshing? Ice cubes! The CUBE functions in Excel are like the ice cubes of the PowerPivot beverage.  They are not required, but just add an element to the drink that makes it so much better.

Once you get to know the CUBE functions you will use them often.  And for good reason.  They allow you to query data from your PowerPivot model outside of a PivotTable.  This means you are not confined to the limits of a PivotTable and can create reports with infinite possibilities.

Since you will likely be spending lots of time working with the CUBE functions, this post will focus on techniques for creating the CUBEVALUE formulas efficiently.  The CUBEVALUE formulas can get long and difficult to read and write.  So it’s best to find ways to save as much time as possible when working with them.

Ice Cubes vs. Crushed Ice

There are two main ways to write the CUBEVALUE function.  I call them the Ice Cube and Crushed Ice methods, and just like their frozen counterpart, the one you use is based on personal preference and maybe the size of your cup (spreadsheet). 😉

Excel CUBE Functions: Ice Cubes vs Crushed Ice Formulas

Ice Cube Method (cell references)

The ice cube method is based on referencing other cells for the member expressions in the CUBEVALUE function.  These other cells contain CUBEMEMBER functions that help determine what slice of data will be returned in your CUBEVALUE formula.

Excel CUBE Functions: Ice Cube Method

When you convert a PivotTable to formulas using the OLAP tools, you get formulas that are automatically created by Excel in the Ice Cube method.  The CUBEVALUE that is created only contains references to other cells, and those cells contain references to the members of the data model.

This is more of an indirect approach.  I call this the ice cube method because the CUBEVALUE formulas tend to be smooth and more uniform in size, but they are hard to consume without breaking them up into pieces.

Pros

The advantages are that the CUBEVALUE formula is short in length.  It is also easy to create if you already have all the CUBEMEMBER formulas setup in the worksheet.

Cons

The main problem with this type of formula is that it is difficult to understand what slice of the data is being calculated by the CUBEVALUE.  If your data model is very simple then you might be able to look at a cell that is referenced in the formula and determine what table or field it is from.  The referenced cell will only display the member name.  If you don’t know what table or field that member resides in, then you have to select the referenced cell and look at the CUBEMEMBER formula to find out.

Excel CUBE Functions: Problems with Ice Cube Method

For example, the following formula references the member expression in cell $E13.
=CUBEVALUE(“PowerPivot Data”,$E$6,<strong>$E13</strong>,G$7)
Cell E13 displays the word “Red”.  To determine what Table and Field “Red” belongs to, I have to select cell E13 and read the CUBEMEMBER formula:

E13:=CUBEMEMBER(“PowerPivot Data”,{“[Products].[SubCategory].&[Road Bikes]”,”[Products].[Color].&[Red]”})

Now I can see that “Red” is a member of the “Color” field in the “Products” table.

You would then have to repeat this process for each argument in the CUBEVALUE formula to get a full understanding of what is being calculated.

This can be a bit time consuming and leads to the Crushed Ice method.

Crushed Ice Method: Full Member Expressions

The crushed ice method refers to CUBEVALUE formulas that contain the full member expressions within the formula.  Instead of referencing other cells that contain CUBEMEMBER formulas, you can add the full string as a member expression argument in the CUBEVALUE function.

Excel CUBE Functions: Crushed Ice Method

The full member expression will look like this: “[Table Name].[Field Name].[Member Name]”

And that same formula will look like the following:

=CUBEVALUE(“PowerPivot Data”,”[Measures].[Transactions]”,”[Products].[SubCategory].&[Mountain Bikes]”,”[Calendar].[FiscalQuarter].&[2]”,”[Products].[Color].&[Silver]”)

You can see that all of the member expressions are fully written out in one formula here.

As a best practice, you will probably want to change the Member Name to a cell reference.  The formula would then look like the following.

=CUBEVALUE(“PowerPivot Data”,”[Measures].[Transactions]”,”[Products].[SubCategory].&[“&$B$4&”]”,”[Calendar].[FiscalQuarter].&[“&D$3&”]”,”[Products].[Color].&[“&$B6&”]”)

This will make the formula much more reusable and allow you to copy it other cells in the worksheet to return different slices of the data.

I call this the crushed ice method because the arguments in the formula tend to be various sizes.  They are easier to consume, but sometimes more difficult to sift through.

Pros

The advantage to the crushed ice method is that you can see all the criteria that is returning the aggregated result in one place.  You do not have to jump to other cells to audit your formula.

Cons

The drawback of this method is that the formulas can get very long and difficult to read and write.  I explain some techniques for making this process easier below.

Cubed vs. Crushed?

Which method is best?  It really depends on the complexity of your report, and also how flexible you want it to be.  If you are building a dashboard with a lot of moving parts and places for user input (slicers, drop-downs, etc.) then the ice cube method might suit you better.  The crushed ice method will probably work best for static reports that don’t change much month-to-month.

Whichever method you choose will depend on the layout and flexibility needs of your report.  I’ve found myself having a mix of both method on the same sheet.  This is probably NOT a best practice, but sometimes it is easier to just write out a CUBEVALUE formula with full member expressions versus having to create CUBEMEMBER cells in a scratch area and then referencing them.

This topic is definitely open for debate, and hopefully we can all learn from your opinion.  Leave a comment! 🙂

Convert the GETPIVOTDATA to CUBEVALUE

When you type an “=” in a cell then select a cell in a pivottable, a GETPIVOTDATA function is automatically entered in the formula.  When the source of the PivotTable is PowerPivot, the GETPIVOTDATA formula will contain the member expressions to the data model.  This is a quick way to get all the member expressions that created the slice of data for the cell you clicked on, and you can use these expressions in a CUBEVALUE function.  The GETPIVOTDATA contains some extra arguments that you will need to delete before using in the contents in the CUBEVALUE function.

There is no way (yet) to create a CUBEVALUE function by simply clicking on a cell in the PivotTable.  This would be a great feature to have in the future.

Convert GETPIVOTDATA to CUBEVALUE

Here is a quick guide to converting the GETPIVOTDATA formula:

1. Type = in a cell then click on a cell in the pivottable. The GETPIVOTDATA formula will be created, click enter.

The GETPIVOTDATA formula contains MOST of the member expressions you will need for the CUBEVALUE formula, and it’s really just a matter of copy/pasting the text to a CUBEVALUE formula.  I say MOST of the expressions because the GETPIVOTDATA formula does NOT contain the member expressions in the filters area of the pivot.  You will have to add those manually.

2. Copy all the text inside the parenthesis ( ) of the GETPIVOTDATA(“copy this stuff”).

3. In a different cell, type =CUBEVALUE(“PowerPivot Data”,

This is the start of the CUBEVALUE function.

4. Now paste the text you copied from the GETPIVOTDATA function at the end of the CUBEVALUE.

5. The GETPIVOTDATA text string contains EXTRA arguments that you need to delete.  GETPIVOTDATA contains a Field and Item argument for each expression.  You do NOT need the Field argument for CUBEVALUE, so you can delete each occurrence.  This  means you can delete every other argument in the text string, leaving only the Item arguments.  You will also leave the measures argument which is at the beginning of the string.

There is a trick that makes this delete process a bit easier.  Select the cell that contains the CUBEVALUE formula and press F2 on the keyboard to edit the formula.  You will typically see a long string of text with all the member expression arguments.

Resize Window to Align CUBEVALUE Arguments

The formula will automatically wrap when it is closer to the right side of the window.  Click the Restore Down or Restore Window buttons on the Excel window, then resize the worksheet so the formula is closer to the right side of the sheet.  You will notice that as you make the window smaller, the text will continue to wrap.  You can usually line it up so that each line contains one argument.

6. Now you just need to delete every other line (argument) in the formula.  The GETPIVOTDATA function contains extra arguments that aren’t required in the CUBEVALUE function.

7. Your CUBEVALUE function should now contain the full member expressions as arguments.  Press Enter to create the formula.  The results should match the original GETPIVOTDATA formula that you used as the source.

Function Arguments Window

When auditing a CUBEVALUE function using the crushed ice method you might run into the issue of not being able to see the member name of the referenced cell.  For example, cell E13 in the formula is hidden, so I don’t know exactly what value is being referenced in this formula.

CUBEVALUE Function Arguments Window Excel

The Functions Arguments window can be used to see the value in E13.  Place the mouse cursor anywhere in the CUBEVALUE function and click the Insert Function button to the left of the formula bar.  This will open the Function Arguments window and the fully evaluated expressions will be displayed on the right side of the input boxes.  If your table and field names are long then it may be difficult to see the member name.  But this is an easy way to see all the member expressions in a list, and it will help you when auditing your formulas.

Converting between Crushed and Cubes

Here’s a quick tip.  If you want to convert a formula from crushed (long) to cubed (compact), you can copy the member expressions in the CUBEVALUE formula and paste them in the CUBEMEMBER formula in a different cell.  You will then replace the original member expression in the CUBEVALUE formula with a reference to the cell that contains the CUBEMEMBER formula.

<Add screenshot>

Closing

The CUBEs are an incredibly useful functions that will allow you to create highly customized reports outside of a PivotTable.  You will probably find yourself using them often when creating dashboards and advanced models.  These tips should help save you time when working with CUBEVALUE formulas.  Please share some of your experiences and tips for working with CUBEs functions.

Click Here to Leave a Comment Below 20 comments
Megan - July 3, 2017

I have a large file with many CUBEVALUE ‘Ice Cube’ style formula.

The file refresh is taking +2 hours and I want to reduce this time.

Some of the CUBEVALUE formula are returning historical data. As this data doesn’t change each time I refresh, I want to break the link to the cube.

How can I break the link to the cube, but still retain a Paste Value record of the data?

I don’t want to paste value the whole range of data as this will overwrite subtotals in the file which I need to remain active.

Thanks in advance!

Reply
Lance - June 12, 2017

Hi Jon,

so in your example of starting with GETPIVOTDATA, can you please show me how to get the “Grand Total” value for one of the attributes in the pivot table?

I’ve tried a few permutations but nothing seems to be working for me.

Thanks in advance,

Lance

Reply
Erin - May 3, 2017

Hello! This article is excellent – thank you so much for this resource.

For some reason, when I try to edit the end of my cubevalue formula to include a relative cell reference (so I can paste down, for example), I am getting an error.

I’ve tried using your format, including quotations and dollar signs, without success:
.&[“&$B6&”]

Do you have any suggestions?
Thank you!!

Reply
    Jon Acampora - June 1, 2017

    Thank you Erin!
    I believe the issue is that the quotation marks need to be on both sides of the square brackets.

    .&"["&$B6&"]"

    I hope that helps.

    Reply
Hunter McKeown - January 6, 2017

Jon did say the below implicitly in his very enlightening (thanks!) explanations above, but I’m just stating it explicitly.

You don’t need to open a pivot table or use the CUBEMEMBER function get a value from a cube. On a blank sheet, you could just type “sales” in cell B1, “north” in cell A2 and write this formula using the CUBEVALUE function to get the amount of sales in the north region. Sorry this example is not related to the images above, but the process would still work.

=CUBEVALUE(“Connection Name”,”[Measures].[“&B1&”]”,”[regions].[“&A2&”]”)

This would return the value of “$1,000,000” (or whatever). Same value you would see in an unwieldy pivot table if you set it up accordingly.

This is extremely useful if you are working with any data model including PowerPivot, but especially a data model from a server that supplies “big data”. You may want reports that don’t involve a pivot table format that you can just refresh easily and this strategy will work well with that. I work in healthcare and our ERP’s main source of data is a Cube that is absolutely massive.

I only use CUBEMEMBER if I want to search which ones are available. I’ve memorized the names of the ones that I use most frequently so that also helps.

These CUBE functions are a GodSend and so are you, Jon.

Thanks!

Reply
    Jon Acampora - January 13, 2017

    Hi Hunter,

    Thanks for the tip! The CUBE functions really are pretty amazing. I think it’s one that not many people use or even know about, which is unfortunate. I’m sure they will gain popularity as PowerPivot does in the future.

    Thanks again for your support. I really appreciate it! 🙂

    Reply
JS - September 23, 2016

Hi Jon – very informative article, thank you! I had a few questions as I have an Excel connected to an OLAP source (SSAS Cube) and am trying to work with the “Convert to formulas” function which removes the pivottable and turns everything into CubeValue functions. I am noticing extremely bad performance as it takes almost 15min to refresh a dataset with around ~1400 records. The client here has large templates as well (>40 columns) so that doesn’t help either. I’ve seen articles on the internet that state this is because one formula per cell = one MDX call for each cell and others that state there should be only a few MDX queries running in the background. If this is how it works, I dont see this as a viable solution but I was wondering if you know of any method to improve this performance? Any other suggestions you have?
Another weird issue I am noticing is that on some excel files, Excel is not asking whether or not to convert the report filters when performing Convert to formulas action. Instead, it is automatically converting them (which is a no-no). I believe this is causing %-age values in the excel to come out incorrectly even though the cubevalue function is correct. I concluded this because when I re-created one of the templates, inserted the same fields from the cube, inserted the report filters and did the convert to formuals, Excel asked me if I wanted to convert filters – I did not and when the load completed, all values were correct. Any help/suggestions you can provide (particularly around the performance issue) would be greatly appreciated. Awaiting your response!

Thanks in advance!

Reply
    Jon Acampora - September 27, 2016

    Hi JS,
    It’s been awhile since I used this feature, and I’m not sure about improving the performance issues. Using 64-bit might improve performance, but I know that’s not a viable solution for everyone. Sorry I couldn’t be more help. Let me know if you find a solution. Thanks!

    Reply
Hi Jon - July 12, 2016

Do you know a method to open an excel workbook (Excel 2010) without updating the Cubevalue and Cubemember excel values from the database?

I have a large spreadsheet which takes 3 hours to update when opening. I’d like to open the spreadsheet without refreshing every time.

Many Thanks

David

Reply
    Jon Acampora - July 18, 2016

    Hi David,
    You can set the calculation mode to Manual, before opening the file. On the Formulas tab in the Ribbon, choose Manual from the Calculation Options menu. I hope that helps.

    Reply
Fernando - May 12, 2016

Hello Jon:

And how can I use CUBEVALUE to return the value for more than one color in your example above? For Example something like this:

=CUBEVALUE(“PowerPivot Data”,”[Measures].[Transactions]”,”[Products].[SubCategory].&[Road Bikes]”,”[Calendar].[FiscalQuarter].&[2]”,”[Products].[Color].&[Black|Yellow]”)

I am using the pipe (|) to get two colors: Black and Yellow

Reply
    Jon Acampora - May 16, 2016

    Hi Fernando,

    You can use the Cubeset function, or use two separate Cubevalue formulas and add them together. I don’t have any articles on the Cubeset function yet, but it will allow you to reference more than one member.

    Reply
juliet - May 10, 2016

hi

after i have enabled my PowerPivot from excel 2013, i cannot see the OLAP tool. i can only sees this

can someone help please, i am trying to use the CUBE VALUE function on my pivot table

thanks

Reply
Chris - March 21, 2016

Hi Jon,

I use cubevalues in a lot of my dashboards however I get an error with Usernames that are stored as numbers would you know how to fix this for example 00test would not work but test01 would work.

using something like this at the moment: CUBEVALUE(“main cube”,$B29,D$20,$E$17)

any help appreciated

Many thanks
Chris

Reply
    Jon Acampora - March 21, 2016

    Hi Chris,
    Are those values stored as numbers in the data model or on the sheet? You should be able to change the data type of the column in the data table in Power Pivot. You can send me an example file at jon@excelcampus.com if you’d like.

    Thanks

    Reply
Nicole Dan - February 1, 2016

Awesome Jon! Thanks a lot!

Apologies for the spelling. It had been a late night.

Kind regards,
Nicole

Reply
Nicole Dan - February 1, 2016

HI Jon, I am a member of the Excel Campus and I am struggling to have the formula converted of a power pivot sort he same way as the original (power piot).

For example I ahve a Y slcier on the power pivot, and the amount by Supplier is being sorted ascending based on Amount by Supplier. Once I choose a FY, the power pivot sorts the Suppliers based on their amount for that particular financial year, however the converted to formula table (via Cube formula) keeps the same order of the Supplier as the power pivot before using the slicers.

IS there anyway I can link the converted to formula table linked to original Power Pivot?

Many thanks for yout hep.

I am anxiously waiting for your response 🙂

With warm regards,
Nicole

Reply
    Jon Acampora - February 1, 2016

    Hi Nicole,

    Great question! You can control the order with the CUBESET and CUBERANKEDMEMBER functions.

    The CUBESET function will return all the members of a field into one cell. It does not actually display all the members in the cell, but this cell can be references by the CUBERANKEDMEMBER function in other cells.

    The reason you use the CUBESET is because it has arguments that allow you to control the sort order based on another field or measure.

    Here is an example of a CUBESET formula that is sorted.

    =CUBESET(“ThisWorkbookDataModel”,”[Category].[Category].[All].children”,”Category Cubeset”,1,”[Measures].[Sum of Revenue]”)

    That formula would return all the members (children) of the Category field from the Category table.

    It is sorted in ascending order (1) based on the Sum or Revenue measure.

    “Category Cubeset” is the caption that is displayed in the cell. You can put any text you want for that. It’s best to add a caption so you know what the cell contains.

    So this cell will indirectly contain a list of all the members of the category field sorted in ascending order based on the revenue measure.

    You can then use the CUBERANKEDMEMBER formulas in the rows or columns area of your converted pivot, instead of the CUBEMEMBER functions.

    Here is what the CUBERANKEDMEMEBER function would look like.

    =CUBERANKEDMEMBER(“ThisWorkbookDataModel”,$B$17,$A19)

    B17 contains the CUBESET formula.
    A19 contains the number 1. A20 contains the number 2. You basically need a row or column of numbers to reference in the CUBERANKEDMEMBER because it is going to return the first, second, third,… member of the CUBESET.

    I hope that helps. Feel free to send me your file if you need help with it. jon@excelcampus.com

    Thanks!

    Reply

Leave a Reply: