• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel CUBE Functions

You are here: Home / Power Pivot / Excel CUBE Functions
CUBE Functions
February 3, 2022 by Mynda Treacy

Some might say that Excel CUBE functions are the new PivotTables because they allow us to build reports based on Power Pivot models in a more free-form fashion. Whereas PivotTables are quite restrictive in their layout and their cells cannot be edited. And just like PivotTables, we can use Slicers to filter CUBE functions for fully interactive reports and dashboards.

Watch the Video

Subscribe YouTube

Download Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook and follow along. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

Writing Excel CUBE Functions

When you’re starting out with CUBE functions, you’ll find it easier to automatically generate CUBE formulas by converting an existing PivotTable like the one below containing sales by category and sub-category for each month.

Excel CUBE Functions

It’s a good idea to set the layout in a Tabular Form so you don’t have redundant headings once you make the conversion.

To convert a PivotTable, select any cell in the PivotTable > then on the PivotTable Tools: Analyze tab > OLAP tools > Convert to Formulas.

convert PivotTables to Excel CUBE formulas

Note: If you have fields in the Filters area, then you’ll be prompted to choose to either also convert them or keep them so you can still filter the data. I prefer to use a Slicer for the filtering, so I don’t have any filters on this PivotTable.

This conversion permanently replaces the PivotTable with CUBE formulas.

In the formula bar you can see the values have been replaced with CUBEVAUE formulas:

Excel CUBEVALUE formulas

And the row and column labels have been replaced with CUBEMEMBER formulas:

Excel CUBEMEMBER formulas

CUBEVALUE Function

Looking at the CUBEVALUE formulas more closely you can see the first argument is the connection, this is your data model for the workbook, which will always be called ‘ThisWorkbookDataModel’, and then we have a series of member_expression arguments which essentially filter the data.

Excel CUBEVALUE function

Member expressions can be other cube functions that evaluate to a set of members or a single member in your model, or they can reference other cells that contain these cube formulas or member values, as we can see with the formula above.

To be clear; Members can be the fields, items, measures, or Slicers in your model.

In the formula above the member expressions are the measure; Sum of Amount, the Sub-category in the row label, the month in the column label and the Slicer for the year.

The Slicer name is entered when the CUBE formulas are cretaed. However, if you need to find the Slicer name, you can right-click and view it in the Slicer Settings. It’s the ‘Name to use in formulas’.

Excel slicer name to use in formulas

Tip: You can change the Slicer name via the Name Manager if you want.

CUBEMEMBER Function

The row and column label cells contain CUBEMEMBER formulas:

Excel CUBEMEMBER function

Notice CUBEMEMBER also requires the connection for the first argument, and then it references the Categories table to get the Category, Charity. These items are hard coded, which is not ideal, and we’ll look at how we can automate this in a moment, but first I want to point out the Grand Total CUBEMEMBER formula below:

Excel CUBEMEMBER grand totals

Here, instead of a hard coded category name, we have [All], which essentially says to include all of the categories in this CUBEMEMBER. Because this formula returns all category names, we can use the last argument for the caption to tell the formula to display ‘Grand Total’ in the cell.  You can change it to display something else if you want.

Now that we’ve converted our PivotTable to cube formulas we can cut and paste them to other locations in our workbook, keeping in mind the cells they reference. And just like any other formula, you can perform further math calculations with them.

Making CUBE Functions Portable

Another option is to nest the CUBEMEMBER values inside the CUBEVALUE formulas to make them more portable.

For example, you could simply copy the CUBEMEMBER formula arguments after “ThisWorkbookDataModel” from the cells the CUBEVALUE formula is referencing creating one big formula like so:

=CUBEVALUE("ThisWorkbookDataModel",
"[Measures].[Sum of Amount]",
"[Categories].[Category].&[Charity]",
"[Data].[Sub-category].&[Donation]",
"[Data].[Date (Month)].&[Jan]",
Slicer_Date__Year)

Now that this cube formula is directly referencing the model, I’m free to move it anywhere in this workbook as it’s no longer dependent on any other cells. The other benefit is this formula is easy to read because all elements are explicitly listed.

Making CUBE Formulas Dynamic

If you’re writing formulas from scratch, it’s a lot of work to create each formula with the category, sub-category and month hard keyed.

Instead, we can make the formulas dynamic by referencing cells containing text values for the row and column labels and even the measure. That is, you don’t have to reference CUBEMEMBER formulas in your CUBEVALUE formulas. This means I can write one formula and then copy and paste them to the cells in the table, which is a super-fast way to write your CUBE formulas and build your reports.

dynamic CUBE formulas

And with all these formulas, I can still use the Slicer to filter the table!

dynamic CUBE formulas

CAUTION: This technique is ok for simple models, like the in this example, however if you’re working with models that have these items listed in multiple tables, it’s better to fully qualify the row and column labels to avoid ambiguous results, like so:

=CUBEVALUE("ThisWorkbookDataModel", $B$6,
"[Data].[Sub-category].["&$C8&"]",
"[Data].[Date (Month)].&["&D$7&"]",
Slicer_Date__Year11)

More CUBE Functions

If you’d like to learn more CUBE functions and Power Pivot, please consider my Power Pivot and DAX course.

CUBE Functions

More Power Pivot Posts

Introduction to DAX

Introduction to DAX Measures

Introduction to DAX in Power BI and Excel. Where and how to write DAX measures, understanding filter context and more.

Toggle Top N with Slicers

Toggle Top N with Slicers using disconnected tables in Power Pivot. These easy measures allow users to choose their top n.
power pivot running total

Power Pivot Running Total

The defaul Power Pivot Running Total setting doesn't handle aggregated periods. This measure solves this issue.
static data tables

Static Tables in Power Query, Power Pivot and Power BI

Use static tables to store data in Power Query, Power Pivot and Power BI without needing to load data from an external source
custom formatting strings in power bi

Custom Formatting Strings in Power BI

Control how data is displayed in Power BI using your own formats. Like hiding negative or zero values, using symbols or custom number formats
GETPIVOTDATA Function for Power Pivot

GETPIVOTDATA Function for Power Pivot

The GETPIVOTDATA Function for Power Pivot references measures and so it works a little different to GETPIVOTDATA for regular PivotTables.
values_as_%_of_another_column_thumb

Show Values as Percentage of Another PivotTable Column Total

Show Values as % of Another PivotTable Column Total can't be done with regular PivotTables, however with Power Pivot we can use a DAX measure.
Change PivotTable Aggregation Methods using Excel Slicers

Change PivotChart Aggregation Methods using Excel Slicers

Change Pivot Chart Aggregation Methods using Excel Slicers and Power Pivot. Disconnected tables and the SWITCH function are the secret sauce.
PivotTable Distinct Count

Excel PivotTable Distinct Count

Excel PivotTable Distinct Count, sometimes called ‘Unique Count’ will return a count of unique items in a field. It uses the Data Model (Power Pivot).
Power Pivot does VLOOKUP

Power Pivot does VLOOKUP

Creating PivotTables from multiple tables used to require consolidating the data into one table, but now Power Pivot does VLOOKUP, kind of. So you don't need to.


Category: Power Pivot
Previous Post:Excel Charts with Shapes for Infographics
Next Post:5 Pro Excel Dashboard Design Tips5 Pro excel dashboard design tips

Reader Interactions

Comments

  1. Andrew Howe

    July 19, 2022 at 3:12 am

    Well this was nice, with a nasty sting in the tail for me at least

    =CUBEVALUE(“ThisWorkbookDataModel”,CUBEMEMBER(“ThisWorkbookDataModel”,”[Measures].[“&”Sum of Amount”&”]”))
    returns 68,566

    So I thought I could generalize with Count, Count Distinct and apply to other fields, without going through the pivot table route, given that the data is already in the data model. But the following all return #N/A:

    =CUBEVALUE(“ThisWorkbookDataModel”,CUBEMEMBER(“ThisWorkbookDataModel”,”[Measures].[“&”Count of Amount”&”]”))

    =CUBEVALUE(“ThisWorkbookDataModel”,CUBEMEMBER(“ThisWorkbookDataModel”,”[Measures].[“&”Count of Sub-category”&”]”))

    =CUBEVALUE(“ThisWorkbookDataModel”,CUBEMEMBER(“ThisWorkbookDataModel”,”[Measures].[“&”Count Distinct of Sub-category”&”]”))

    The technique would be incredibly useful as a quick investigation of data within data models if the above worked.

    Reply
    • Mynda Treacy

      July 19, 2022 at 9:16 am

      Hi Andrew,

      Those measures are implicit. You need to create a PivotTable with those implicit measures before the CUBE functions can see them in the model. You can then delete the PivotTable. Alternatively, and better is to write the measures explicitly in the model and then reference them with the CUBE functions.

      Mynda

      Reply
  2. Mustapha

    February 4, 2022 at 9:00 pm

    Hi Mynda,

    How do you add a slicer to the CUBEVALUE formulas for a field that is in a linked table in the data model? In your file for example how would I add the Category as a slicer in to the formula? I can add sub-category but get #VALUE!.

    Reply
    • Mynda Treacy

      February 5, 2022 at 4:03 pm

      Insert the Slicer, then right-click and edit the properties to get the Slicer formula name. Insert this in one of the member expression arguments, as you can see in my example.

      Reply
      • baxbax

        February 10, 2022 at 4:01 am

        Hi Mynda,

        That works fine for any field that is in the Data table but not if the field is in the Categories table even though they are linked in the Data Model. As mentioned I tried it with the Category field. The slicer formula name is “Slicer_Category” but if you add this in to the formulas it gives #VALUE!.

        Reply
        • Mynda Treacy

          February 10, 2022 at 5:33 pm

          I suspect you have conflicting filter context. If you use this formula based on my file references it works:

          =CUBEVALUE("ThisWorkbookDataModel",$B$5,P$6,Slicer_Date__Year1, Slicer_Category)

          Mynda

          Reply
  3. Tom

    February 4, 2022 at 3:54 am

    Hi Mynda,

    Thanks for all you do and all the fantastic info you post/share. Sorry if this is a “newb” question, but how to you get the “OLAP tools” button to show and not just be grayed-out? I tried to convert a pivot table I had previously created and couldn’t. When I web-searched for an answer all the answers that came up said that this tool was only for pivot tables which were connected to an OLAP source (i.e. an external SQL source). It this true, and if not, how can I use this tool for my pivot table?

    Thanks for your patience. Cheers, Tom.

    Reply
    • Mynda Treacy

      February 4, 2022 at 9:58 am

      Hi Tom, the OLAP tools are only available for Power Pivot PivotTables. As shown in the video at the start, you have to check the ‘add to data model’ button when creating your PivotTable for this to work. Hope that clarifies things.

      Reply
  4. Ashok Patel

    February 4, 2022 at 3:04 am

    I have Excel 2013 and, in my pivot table, Analyze tools setting OLAP function is grayed out. How do I enable it? Your article does not say anything about this issue.
    Thanks

    Reply
    • Mynda Treacy

      February 4, 2022 at 9:57 am

      Hi Ashok, you need to load your data to Power Pivot for the OLAP tools to be available. Not all versions of Excel 2013 have Power Pivot. You can check the list of versions that do and don’t have Power Pivot here.

      Reply
  5. Philip T

    February 4, 2022 at 1:41 am

    Another excellent video, Mynda. thank you. I am puzzled by the case where there are imbedded spaces in the member-name. In your workbook, I pointed a CUBEVALUE function to “Dining Out” in column B.

    If you paste-values the CUBEMEMBER function as text, the CUBEVALUE function works fine. If you type in the text or, after pasting, edit it in anyway (delete a letter then put it back in), the CUBEVALUE returns #N/A. Wrapping the text in square brackets solves the problem but looks ugly. Strange.

    Reply
    • Mynda Treacy

      February 4, 2022 at 9:50 am

      Interesting find, Philip. I haven’t been able to replicate this glitch on my PC.

      Reply
      • Philip T

        February 4, 2022 at 11:59 pm

        I did a Data. Refresh All after the edit. Same result on “Sum of Amount”. All CUBEVALUE functions returned #N/A.

        Reply
        • Mynda Treacy

          February 5, 2022 at 3:49 pm

          Refresh All worked fine for me. Very strange. If you want to email me your file I can take a look.

          Reply
          • Philip T

            February 6, 2022 at 7:05 am

            Thank you Mynda. I have replied (with file attached) to the newsletter email address.

  6. Paul Martin

    February 3, 2022 at 10:35 pm

    Whilst we are all trying to promote cool tips and tricks regarding Excel (and you do this better than most), I think we need to give balanced and honest feedback re the downsides of using certain functions and the CUBE functions DEFINITELY fit into this category. I saw this “old and scarcely used for a good reason” function trotted out again at the London Excel User Group in December. People lost their minds through excitement and I am thinking :

    i) Doesn’t scale at all (architecturally when used against SSAS Tabular or MD Cubes) at worst it performs one query PER CELL. You can guess how efficient this is ! There is an obvious reason why it says “getting data” in each CUBE function cell on certain reports.

    ii) It is very hard to maintain. Yes, convert to formulas is lovely on a new report / web demo and you simply cut and paste formulas as required. You now add 10 nominals to the GL next month and want to insert these into the report ? Uh, not so easy. Nothing can be data driven, so you’d better check EVERY CUBE function report EVERY month / period, as you’d better hope the data source doesn’t change !

    So if you get excited by the CUBE functionality, my advice is test WITHOUT rolling out and see if it performs as expected. Then and ONLY then inform your users.

    Reply
    • Mynda Treacy

      February 4, 2022 at 9:41 am

      Thanks for sharing your experiences, Paul! I agree, they have their downsides and are better suited to models that are more static in their structure. I like them for pulling in a few values here and there that don’t warrant a bulky PivotTable.

      Reply

Leave a Reply Cancel reply

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

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.