Excel CUBE Functions can do everything a PivotTable does and more!

This is groundbreaking lol. I thought i already know most of excel. This just adds a new dimension

It is a good feature but i cant see how this can be useful if i need to refresh my data constantly. If it weren't for that it would be ideal, so maybe GETPIVOTDATA() still better for me

Great Video! I hardly can find any video on cube formulas in depth. Thank you! If you can make future videos, please let me know how to qualify the formula on a date MM/DD/YYYY.

Probably this is one of the best Excel cube function video available online! Additionally, the accompanying file is incredibly valuable. But, despite my best efforts, I couldn't find a solution for running the CUBEMEMBERPROPERTY function within it; it consistently returns N/A. Try as you might, you cannot find a working example of this function anywhere.

Wow, this is one of the best videos on Cube functions which not only builds solid foundation but also builds on it. 👍👍

Are there any performance concern with cube functions vs a traditional pivot table? I’m creating a large report that will require a few pivot tables or cube tables if chosen

Merci beaucoup Mynda! Thanks a lot! Very usefull. And I really like the technique of converting to formulas.

Thank you so much for this amazing video. I love the Cube fonctions of Excel. I have a question for you : I have a table which contains the cost center codes and another table which contains the cost center codes and their names. the two tables are linked in Power Pivot. Do you know how I can retrieve the name of a cost center from its code with Cube fonctions please ?

ty

Hey! Great channel!

I have a question about using drill-through in a report with cube functions vs pivot table.

I've been using cube functions built on top of the cube generated from a PowerBI dataset and accessing that data via Get Data inside Excel. I build my pivot table to generate the correct syntax then convert the pivot to formulas and then I'm off and running.

I've successfully built formatted financial reports using the model/measures that I've developed in PowerBI, but I was wondering if there was a way to add drill-through capability from a cell value containing a cube function? I've successfully maintained the DETAILROWS expression via Tabular Editor external tools on PowerBI desktop which has given me the ability to drill through into my pivot table values when I'm using a PowerBI measure stored on a measure table (no rows to drill through).


But now I want to be able to drill through into the details from my cube function cells. I've seen a couple people using a right-click and then additional actions > Drill Through, but I think those cubes are built in SSAS and not PowerBI datasets.

Is this possible?

WOW!!!! I agree totally with Jerry Dellasala! This is fantastic!!!

Hi Mynda,
Here my question for you: how can we generate the lists of unique row/column headers from the data model using CUBE functions?
Thanks!

Thank you, you are great. Have a wonderful day :)

After conversion from a pivot table to a CUBE, does the CUBE refresh like a pivot table?

Wow.. great...so easily briefed.

This is only for static data? If I update tables this wont 'refresh' like a pivot table?

MOTH thank you for this session. Can I ask, if I had a few pivot tables but I wanted to use one slicer where the data related to a date table in the model but one of them didnt, could I use the cube feature to hard code the slicer reference and that would update both pivot types?

Can CubeValue functions also be referenced to Dynamic Arrays?

Hi ...
How to convert cube function back into pivot table ?

Just to be sure, the data must start from a Power Pivot table?

Hi Mynda, is it possible to add a calculated field to a Olap Cube based pivot table WITHOUT converting to formulas? I cant seem to do it.

EXCELlent video!! Thanks a lot!!

Hi Mynda, I so happy that you made a video on the CUBE function. I’m using it in combination with Power BI data models and the possibilities are just amazing. Keep up the great work!

I'm excited to try this out. While the syntax is more complicated, it may scale better than SUMIF and SUMIFS aggregate functions in terms of performance and memory. Not all my lists can be data tables, (e.g. data retrieved by a special Add-In), but for those that are, what an awesome technique.

What is the difference between this & sumifs function? Thanks in advance.

Sometimes the algorithm bring us amazing surprises. Thank you for the video! I'll start using it tomorrow!

It flew over my head, but thank you

BUT... you cannot drill down. That is where PivotTables will always win.

Microsoft does a poor job promoting/advertising new features like these

Incredible...just incredible the way you explain the features and benefits
Speechless...i definitely use this in work...you're a Shifu
Thank you

I used this to solve the problem of being able to interactively sort what was a Pivot Table by one of the values columns (which happens to be a measure). Thanks for the heads up… yet another function I had no idea was staring me in the face.

Yes, aware of these, however for some reasons haven't found a lot of use for them.

The main issue I've always had with pivot-tables as it's hard to make them dynamic or 'portable'. These functions may very well solve this problem. I'll play around with it a bit. Great video and great functionality, thanks for sharing!

Mynda Hi,
Thank you for this magic function and it is so super exciting for every excel advanced users.
I'm wondering that how can we use "{" combination with "&" symbols and cell referance like B6 together when we would like to find out the CubeValue function. I didn't find result by combining those symbols.
Could you please help me to write correct syntax as below:

=CUBEVALUE("ThisWorkbookDataModel";"[Measures].[Total Data: Amount]";{"[Categories].[Category].&[Charity]";"[Categories].[Sub-category].&[donation]"};"[Data].[Date (Month)].&[Jan]")

I think this is great when some of the values you want does not slice the same way as the others. This adds flexibility so that you don't need multiple pivot tables for this task. Although the head start takes a little longer, but I think it's great for certain scenario and allow for more options, thanks for sharing this, wasn't sure what is the use of these functions before!

Hi Mynda, thanks for this sharing. however, my slicer disconnected after the conversion. I'm not sure why.
thanks

Is it possible to insert a line ?

hi ma'am... i'm working as inventory controller in a auto spare parts company...i am using excel as my monitoring especially in ordering and receiving... currently, i am using filter to identify which car has an order for a particular part...now, want to change this process...what if i want to know which car in same model i am going to serve the part especially if the has only few parts remaining or only that car order that part...
ex... Part Number YYYY
car 1 oder
PN YYYY
PN NNNN

car 2 order
PN YYYY

in filtering scenario, if i'm going to filter YYYY, both cars will appear but i can't see which one has only 1 part order that for me, i should serve the part first in order to release the car from workshop...

thank you and hopefully you can help me on my problem...

Thank you SO MUCH for this! I've watched at least a thousand Excel videos, and CUBE functions may have been mentioned once or twice in passing, never explained. I have never had the need to use them, but that's how Excel is - you don't even know you need them if you don't know about them! Great video.

Hi Mam,

I want to learn MS Excel

This is amazing, thank you so much for sharing xx

Hi Mynda, thx for another great video.
As I have intermediate (bit of a stretch 🤔) knowledge of excel, where I currently use tables and pivot tables for "getpivotdata" function to generate weekly and monthly reports. I have been a bit daunted and putting off a move from excel to a power bi dashboard/report, can the cube example you demonstrated be used as a replacement, as many videos say no pivot tables in power bi?

I used Cube formulas to create a flexible report but found as the underlying dataset grew it took longer to refresh the data than a pivot table. Mynda, have you had this experience?
PS great video!

Excellent Mynda, now can get rid off my hidden underlying xtables (I was using the read table function). One question though. Do you know if it is possible to keep the double click drill down feature of the xtable with cubevalue ? Would be so cool to shift double click on the cell and get the underlying data...

Hi Mynda. Seems daunting at first. But like anything else in EXCEL, break it down to the components and it makes sense. Thanks for the lesson :)) Thumbs up!!

Thank you so much for this.

Very useful ; something worth exploring. Many Thanks