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

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.

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.

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:

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.

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.

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

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.

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.

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.

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

• Nicole Dan says:

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

• 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. [email protected]

Thanks!

• Christann says:

Thank you so much for the post and also this clearer reply to Nicole Dan! This cleared my many questions about CUBE functions. I hope to start using them right away!
–Christann

• Javed Haider says:

Hi Jon,

I am using this Cubeset and Cuberankedmember to get required data sorted in descending order. I am trying to bind a slicer (created from data model & is text string) to this cubeset to see data only for selected items. I created a measure using MIN function. It works fine when I select one slicer and data is sorted perfectly but when I remove all slicer to view all data or select multiple slicer it won’t work sort the data in order. Cube formulas and measures I am using are given below:

Cubeset Function:
=CUBESET(“ThisWorkbookDataModel”,”[Table1].[Subcategory].children”,”Applications”,2,”{([Measures].[_Subcategory],[Table1].[Operating group].[“&A2&”])}”)

Cell A2 in cubeset above is
=CUBEVALUE(“ThisWorkbookDataModel”,”[Measures].[RankOG]”,Slicer_Operating_group)

RankOG Measure:
MIN(Table1[Operating group])

Many Thanks.

• Nicole Dan says:

Awesome Jon! Thanks a lot!

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

Kind regards,
Nicole

• Chris says:

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

• 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 [email protected] if you’d like.

Thanks

• juliet says:

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

• Fernando says:

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:

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

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

• Hi Jon says:

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

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

• JS says:

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!

• 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!

• Hunter McKeown says:

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!

• 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! 🙂

• Erin says:

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

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

• Lance says:

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.

Lance

• Megan says:

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.

• Joseph Finnerty says:

Hi – Did you ever get an answer to this from this site or another site? My previous employer had created a macro to be able to do this which consisted of selecting the tabs with cubevalues on it that you wanted broken and it would save the tabs a new workbook, breaking the cubevalues but keeping the subtotals in. I am looking for something similar but can’t seem to find it anywhere on the web.

• Hi Megan and Joseph,

I’m sorry to not get back sooner. There are probably a few ways to go about this. Since you only want to “break” or paste values for some of the formulas, here is one way I can think of.

1. Select the range that you want to paste values. You can also select the cells that contain subtotal formulas.
2. To then select the cube formula cells only we can use the Find window (Ctrl+F).
3. Type CUBE in the Find what box.
4. Click the Options button in the Find window and choose Look in: Formulas.
5. Click the Find All button
6. You should see all the cells listed in the list box in the Find window.
7. Select the first item and then press Ctrl+A to select all the items in the list. This will select all of the cells that contain a cube formula within your original selection.

8. Run the following macro on the sheet.
Sub Paste_Values_Selection()

Dim c As Range

For Each c In Selection.Cells
c.Value = c.Value
Next c

End Sub

The macro will replace all the formulas in the selected cells with values. Make sure to SAVE the workbook first and/or run this on a copy of the workbook. You will not be able to undo this change.

We could also right the entire process into the macro. It is nice to see the selected cells before deleting them, which could also be written into the macro.

I hope that helps.

• jens says:

Hi Jon,

I try to create a Cash flow dashboard where I would like a Cubevalue function to return sum before a date. I want to be able to type a date in a cell and then the cubevalue should sum up a specific account all values before the date typed in the cell. Well I would like to use comparison operators in Cubevalue.

thanks a lot.

I hope you have a solution.

Kind regards,

Jens

• Shriram says:

Jon,
I am trying to retrieve a field from an OLAP / SSAS cube using CUBEMEMBERPROPERTY function. Its not very well documented and the use cases dont work on an OLAP / SSAS Cube. For example I am trying to retrieve a description of a field using its code.

Do you have any experience using this function especially on OLAP / SSAS cubes ?

thanks
shriram

• Sylvie says:

Dear,

Is it possible to select the date and hour F12 2016 23:59:59 in the cubemember formula as I need figures that of the aacounting year 2016 that are not yet closed?

Thanks!

• Rushi says:

Hi Jon,

I recently started learning CUBE formulas. This page is very useful, thank you

One quick clarification. Following your method, I applied below formula to get the values

=CUBEVALUE(“ThisWorkbookDataModel”,”[Measures].[“&G14&”]”,”[FSale].[Product].&[“&F14&”]”,Slicer_Discount1)

However, I get the same result by changing the formula as below.

=CUBEVALUE(“ThisWorkbookDataModel”,\$G4,I\$1),Slicer_Discount1)

Note: values at G4 and I2 are not formulas but manually typed text headers. Just curious when the second formula gives the same result why the first approach should be used?

• Alessandro says:

This unfortunately doesn’t work when referenced cell contains square brackets:

=IFERROR(CUBEVALUE(“SALES_BMS_HIST”,”[Measures].[Revenue PowerEdge]”,”[Management Geography Hierarchy].[Geo Country].[“&\$C143&”]”,”[Account – Distributor].[Disti Sub Account Name].[“&\$B143&”]”,Slicer_Fiscal_Calendar,Slicer_Orders),0)

if I evaluate the formula with B143 containing INGRAM MICRO Magyarorszag Kft [Project], the “[Account – Distributor].[Disti Sub Account Name].[“&\$B143&”]” part transforms to: *”[Account – Distributor].[Disti Sub Account Name].[INGRAM MICRO Magyarorszag Kft [Project]]** so it leaves a trailing bracket at the end.

Any idea how to fix this? Tried with various double quotes combinations but nothing 🙁

Thx!

Hi
I was previously trying to use the following cube formula
CUBEVALUE(“ThisWorkbookDataModel”,CUBESET(“ThisWorkbookDataModel”,(CUBEMEMBER(“ThisWorkbookDataModel”,”[Range].[ASSIGNED_GROUP].[Group 1]”),CUBEMEMBER(“ThisWorkbookDataModel”,”[Range].[ASSIGNED_GROUP].[Group 2]”))),”[Measures].[Ticket Count]”)

It just was not working.. However then I ended up doing this:

=CUBEVALUE(“ThisWorkbookDataModel”,CUBESET(“ThisWorkbookDataModel”,”{[Group 1],[Group 2]}”),”[Measures].[Count of INCIDENT_NUMBER]”)

Vola, works!
But what gives? What is the significance of using { this bracket type to enclose {[Group 1],[Group 2]}? How is it that I dont even have to use a reference to a CUBEMEMBER?

Also I notice you would use the &[Road Bikes] in your formula. Is the & a necessity? Mine seemed to work fine without it….

Thanks

• Merrill says:

Is there a sample data file available for this?

(Thanks, and great post!)

• Jeremy says:

I use CUBESET very successfully to build definitions of “year to date” for financial reporting purposes. However, one problem I encountered back in 2009 (and still an issue) was that there is a 256 character limit on formulae in excel. It is very easy to quickly exhaust this limit with these formulae, especially when using CUBESET with many different members (say in my case, when we get up to 7-12 financial month-related members). The only alternative is to first build the required full string as text in separate cells and refer to the final result cell in your CUBESET formula. This cheat should not work but actually works great. Another nice point about the CUBE.. functions is that you can refer to them anywhere in your workbook and using any other excel formulae and functionality to do that – ie: v/hlookup, match, offset, indirect, named ranges, validation drop down lists (like a menu), etc etc. The workbook ALWAYS understands that what you have referred to an cube-related object and treats it accordingly. It allows to create simple forms requiring very few selections which then drive MANY changes in the final reporting. (fullest marks possible to MS for an excel-lent job here!)

• Del says:

Hello Jeremy, could you please guide me on how exactly you’re referencing the cells when it comes to breaking down a CUBESET formula.
I divided the following part of the formula [Pivot].[Period].&[2020] as follows:

[“&A10&”].[“&A11&”].&[2020] with A10 and A11 containing the words “Pivot” and “Period” respectively.

However, an error appears. Could you please elaborate on your method to split really long CUBESET formula owing to the character limit that excel has.

• Nicholas says:

Not a clear description of the function. What are “PRODUCTS” and “CALENDAR” a reference to? This can be assumed based on the example, but it is not explicit. Why are there 3 variable references?

• MD. KHURSHED ALAM says:

My slicer work in pivote table but do not work when I convert it to formulas why?????

• Tyler says:

This is still so helpful years later

• Humberto says:

Thank you very much for this post very useful!!