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.
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!
Dear Mynda, thank you very much for this very interesting video. I tend to use cube functions more and more as they allow more flexibility than a regular pivot table.
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 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. 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!!
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.
As long as your dates are proper date serial numbers, then you can always reference the date cell irrespective of whether it's formatted as mm/dd/yyyy or dd/mm/yyyy.
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.
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.
@BenoitLamarche Did you find percormance advantages in Cube formulars vs. SUMIFS? I have P&Ls that reference over 200K lines and SUMIFS tend to get somewhat slow at some point. Would really be interested in a performance comparison.
7:59 again, it's important to include Category in the formula specially if your Sub-category appears in multiple Categories, otherwise your data will be wrong. thanks a lot for explaining how to make cube formula dynamic. i struggled on this part.
No need to include the category in the member expression arguments because the sub-categories have a one to one relationship with the category, i.e. it's not possible for the Donation sub-category to be related to any other category than Charity.
Mynda, I love your videos, but I'm not sure about the advantages in relation to a pivot table. From what I've seen, they seem quite similar, the only difference is that in Pivot tables we cannot make changes as you showed in this video using the Cube function. Is there something else I cannot see clearly?
With CUBE functions you're not restricted to a PivotTable layout, so they're great if you want more flexibility in your report appearance. You can also add further calculations to the CUBE formulas, which you can't do in the PivotTable values area. And if you're pulling a load of single values from a model where you'd normally build a load of single value PivotTables, then you could more easily do this with CUBE formulas.
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?
Glad you've found my channel helpful 🙏 I've not heard of CUBE formulas allowing drill through, sorry. Have you considered setting up a separate sheets at the drilled through level of data you want to see and connecting it to a Slicer to allow the user to choose the items they want to see?
Thanks Mynda, however the use case is not clear to me from this video - where is the value? Now fully immersed in pivot tables and the calculations they bring, I don't want to go back to formulas - would this give faster creation of complicated formula we might have previously done in a calculated field or measure, but easier? And, in your example, I usually have ranges that extend into the future that update automatically when I paste new data with future dates in the 'data' tab for example - will we need to paste forward these formula then so that a date auto adds rather than it auto-updating as a pivot table would? Thanks as always
Hi Alex, the value is that you can create a report that isn't restricted to a PivotTable layout and formatting. You don't need all the headers and bulk of a PivotTable and can add additional calculations to the CUBE formulas, which you can't do in a PivotTable value cell. I haven't tested them with dynamic arrays that spill for automatic updating of ranges, but it may work. Otherwise, you'd have to copy them across/down for the next month. I personally like CUBE formulas for headline type reporting where I only want one value here and there and don't want to build a whole PivotTable to extract it.
Me Too..I am her viewer and fan since long time. She is always on it. But I think it takes few more videos to master the concept. I am PQ user since it was new.
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 ?
Do we need make each and every cell independent in same way. It will be lot of manual effort. Is there any easy way to make independent all of the cells ?
Is there a way to generate the Category and Subcategory also from the cube so that they are dynamic too? In my case, my slicer has Project - so, when I click on it, the Category and Subcategory list gets changed, hence all the values become empty. How do I make that dynamic?
Nice I was always wondering how to use this feature. It might be useful to build some static reports, however I find the biggest drawback vs PowerPivot is that the data is no longer "in one piece" if you know what I mean. Still, for static reports, this may be very useful.
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, 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?
Hi Tunde, there aren't PivotTables in Power BI in the same degree that we have them in Excel, but there are matrix tables in Power BI which are similar. You can't use CUBE formulas in Power BI, so not sure what you were hoping to do there.
@@MyOnlineTrainingHub Thx for taking the time to reply, I just need to take the bull by the horns, copy my data in look into power bi and try matrix tables along with the other vizulisations 😆
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!
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
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?
No, data validation can't filter PivotTables or CUBE functions. You'd have to create your reports with SUMIFS etc. It'd be best to add your other data to the data model and create a relationship to the Slicers through dimension tables. You can learn more about this in my Power Pivot & DAX course: www.myonlinetraininghub.com/power-pivot-course
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]")
Thanks, Tina! There’s no option to default to data model AFAIK, but you could use Power Query to import the data and in there is a default load option. However, it’s probably overkill if you’re just getting data from the worksheet and not an external source.
@@MyOnlineTrainingHub thank you!!! I will follow up- i work with data from multiple workbooks, and i do a lot of different pivot tables in many additional sheets. My final workbook with the query and pivot tables is i believe overloaded. Can you please suggest how to continue so it does not take a lot of memory. Basically, I am using excel as a small costs database.
Perhaps separate your data workbook and your analysis workbooks. Use Power Query to get the data from your data workbooks and bring it into another file for your analysis.
@@MyOnlineTrainingHub that is really good tip, actually. I can bring the data into the another workbook w power query data model option, so it will not take that much space. Thank you a lot
Hi Mynda, i wonder if i could bother with the following question? Do u have a video/tutorial that explains the possibility of ‘importing’ an Excel power pivot model into Power Bi, but with the possibility of updating my original Excel workbook and ‘push through’ to Power Bi the periodic updates that i edit in the original Excel wirkbook? Thanks Martin ( South Africa)
Hi Martin, I don't have a UA-cam tutorial, but cover this in my Power BI course: www.myonlinetraininghub.com/power-bi-course Via the File tab in Excel > Publish > Export Workbook data to Power BI will do what you want. You may need an on-premises gateway to refresh, depending on your source data location.
You need to use the CUBESET function with CUBERANKEDMEMBER for this, which I cover in my Power Pivot & DAX course: www.myonlinetraininghub.com/power-pivot-course
@@MyOnlineTrainingHub Yes indeed. I found that out myself, yesterday. I find it rather convoluted (…). My first solution was to use a DAX measure to CONCATENATEX all the unique values together with a “,” (and sort then, of course). Then in Excel simply call that measure as a VALUE and TEXTSPLIT it. Honestly that is way easier, IMO. Also, that total-on-top is not issue this way, and it can always be VSTACKed at the bottom (or HSTACK as the case may be). In general I find CUBE functions to be rather clunky to use and the fact that they do not uniformly vectorize in their scalar arguments doesn’t make it any easier. Then again it is a structured way of accessing the data model… :-)
Yes it will. If you update the source data for Power Pivot you can refresh the connection and PivotTables and it will feed through to the CUBE functions. Ideally, you should use Power Query to get the data to load to Power Pivot/Data Model which the PivotTables are built on.
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...
Mynda, can you please tell me why? when I convert the pivot table column headers not getting converted to cube member or whatever it's called. Because of this my slicers not functioning
Hi Anuri, I'm not sure what's causing this. Are you certain the column headers you're referring to are actually PivotTable fields and not manually entered headers? If you'd like me to take a look, post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub yes they are pivot table column headers. I think reason could be bacause of drop down arrows in the column headers . Can you please tell me how to remove the drop down arrows. I tried from pivot options by unticking display feild captions and filter drop downs but then it hides everything
Charity is a sub-category of donation, so it goes without saying. i.e. donation is not a sub-category of any other category. It's a one for one relationship.
Hi Mynda, please advise if this automatically updates just like PivotTable? For example I go into a new month (2022 May) will it automatically add the new month or do I have to add this manually by following the formula? Another question is, I tried putting this into a table (Insert > Table), and my Cube calculations become #N/A but the formula is exactly the same, do you know why this happened? Thank you!
HI Jon what's your use case? I build Angular web apps that achieve more than Power BI for most use cases, but love excel for fast data analysis. Never found the sweet spot to get heavily into Power BI does it benefit you?
Using Power BI would be even more inflexible...the idea is to have the data in Excel in any format you want. Don't get my wrong, I love Power BI too. I cover the pros and cons of Excel vs Power BI here: ua-cam.com/video/0nAQ7gPO_6Y/v-deo.html
@@al3xj that’s cool Alex, I’ve been trying to lean angular. Power BI’s visuals are much easier to use and more appealing than excel, and it’s free on desktop if you’re just trying to create visuals and share them in the same way they are showing here in excel
@@MyOnlineTrainingHub what your category or subcategory changes? It seems like you’re coded a static number of items for each, or am I misunderstanding how this works?
@@MyOnlineTrainingHub can you explain what can’t be done in power bi? I’m not following. It looks like a matrix visual. Is it that in theory you could source the data from multiple cube models in any cell you want?
pretty confusing I have heard for the first time on this topic, that's a mistake to address such ambiguous topics without any prerequisites and elementary points
You can always stick with PivotTables, Christina 😊 Can't get much easier than having Excel write all the formulas for you in the PivotTable background.
This video is terrible. You didn't say when you change the sheet in 0:36. It took me ages to figure that out. OFC when I changed the sheet I have no access to the 'design' or 'analyse ribbon', bc you didn't explain how to do that. So I opened another pivot table, but OLAP tools icon is unavailable. You just waste my time. Please record another video, were you teach not waste people's time. Or just delete this video.
also you lie on your website. Your wrote 'To convert a PivotTable, select any cell in the PivotTable > then on the PivotTable Tools: Analyze tab > OLAP tools > Convert to Formulas'. Which is a lie. OLAP tools are grey, unclickable
If your OLAP tools are grey then you haven't loaded the data to the data model (Power Pivot), you must be working with a regular PivotTable. I state this at 0:21.
Hi Mynda. Very interesting to make pivot tables dynamic. I just have a problem to make a date column dynamic. Can I ask you for help? French version. =MEMBRECUBE("ThisWorkbookDataModel";"[Relevés_St_Marc].[Date].&[2022-10-01T00:00:00]"). Thanks in advance.
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.
You're very welcome, Jerry! 😊
Wow, this is one of the best videos on Cube functions which not only builds solid foundation but also builds on it. 👍👍
Wow, thanks so much!
Great video! CUBE functions are some of the best, if not the best, features in Excel. I've always wondered why they aren't more popular
Totally agree!
WOW!!!! I agree totally with Jerry Dellasala! This is fantastic!!!
Great to hear 🙏
OK, this is huge. I never knew about these functions. Thank you for the education!
My pleasure, John.
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!
Glad you've got a use for them!
I have to check these out, Mynda, I have never used CUBE functions. Thanks!
Hope you like them, Chris!
Sometimes the algorithm bring us amazing surprises. Thank you for the video! I'll start using it tomorrow!
Great to hear, Breno!
Thank you so much Mynda for this amazing video, so easily explained. I've watched many other videos but couldnt wrap around my head to it.
So glad it was helpful! 😊🙏
Dear Mynda, thank you very much for this very interesting video. I tend to use cube functions more and more as they allow more flexibility than a regular pivot table.
Glad you liked it 😊
Incredible...just incredible the way you explain the features and benefits
Speechless...i definitely use this in work...you're a Shifu
Thank you
😁 thanks so much, Larry! You're too kind.
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!
Wow, that's great to hear!
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!
Great to hear you'll find them useful, Eric!
This is groundbreaking lol. I thought i already know most of excel. This just adds a new dimension
Glad you enjoyed it 😁🙏
You are the best teacher. Thank you
Wow, thank you! 😊
already new this, but I am so grateful because you got me going with excel datamodels in 2019 when I was dumb as F :D
Glad it was helpful, Calvin!
Merci beaucoup Mynda! Thanks a lot! Very usefull. And I really like the technique of converting to formulas.
Glad it was helpful!
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!!
Cheers, Wayne 😊
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.
As long as your dates are proper date serial numbers, then you can always reference the date cell irrespective of whether it's formatted as mm/dd/yyyy or dd/mm/yyyy.
Thank you, you are great. Have a wonderful day :)
Thank you! You too!
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.
So pleased to hear this was useful!
Very useful ; something worth exploring. Many Thanks
Great to hear 😊
Wow.. great...so easily briefed.
Thanks so much 🙏
Wow!! Mynda you are a true Excel Genius!! Thanks for sharing. Best regards and best wishes for '22. Peter Lloyd
Thank you! You too, Peter!
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.
Glad to hear it might be useful to you, Benoit!
@BenoitLamarche Did you find percormance advantages in Cube formulars vs. SUMIFS? I have P&Ls that reference over 200K lines and SUMIFS tend to get somewhat slow at some point. Would really be interested in a performance comparison.
EXCELlent video!! Thanks a lot!!
Thanks so much, Felipe!
7:59 again, it's important to include Category in the formula specially if your Sub-category appears in multiple Categories, otherwise your data will be wrong. thanks a lot for explaining how to make cube formula dynamic. i struggled on this part.
No need to include the category in the member expression arguments because the sub-categories have a one to one relationship with the category, i.e. it's not possible for the Donation sub-category to be related to any other category than Charity.
Hi Mynda!Great Refresher Tutorial,I Had To Learn Cube Functions For The MOS 77-728 Expert Exam...Thank You :)
You're very welcome, Darryl!
Mynda, I love your videos, but I'm not sure about the advantages in relation to a pivot table. From what I've seen, they seem quite similar, the only difference is that in Pivot tables we cannot make changes as you showed in this video using the Cube function. Is there something else I cannot see clearly?
With CUBE functions you're not restricted to a PivotTable layout, so they're great if you want more flexibility in your report appearance. You can also add further calculations to the CUBE formulas, which you can't do in the PivotTable values area. And if you're pulling a load of single values from a model where you'd normally build a load of single value PivotTables, then you could more easily do this with CUBE formulas.
That is cool when moving long formulas across as not having to hardcode the referencing every time or data ranges, very portable. Thanks!
My pleasure 😊
This is amazing, thank you so much for sharing xx
Glad you enjoyed it!
Thank you so much for this.
My pleasure, Jose!
After conversion from a pivot table to a CUBE, does the CUBE refresh like a pivot table?
Pivot is great report with drill-down function
Glad you liked it 😊
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?
Glad you've found my channel helpful 🙏 I've not heard of CUBE formulas allowing drill through, sorry. Have you considered setting up a separate sheets at the drilled through level of data you want to see and connecting it to a Slicer to allow the user to choose the items they want to see?
Thanks Mynda, however the use case is not clear to me from this video - where is the value? Now fully immersed in pivot tables and the calculations they bring, I don't want to go back to formulas - would this give faster creation of complicated formula we might have previously done in a calculated field or measure, but easier? And, in your example, I usually have ranges that extend into the future that update automatically when I paste new data with future dates in the 'data' tab for example - will we need to paste forward these formula then so that a date auto adds rather than it auto-updating as a pivot table would?
Thanks as always
Hi Alex, the value is that you can create a report that isn't restricted to a PivotTable layout and formatting. You don't need all the headers and bulk of a PivotTable and can add additional calculations to the CUBE formulas, which you can't do in a PivotTable value cell. I haven't tested them with dynamic arrays that spill for automatic updating of ranges, but it may work. Otherwise, you'd have to copy them across/down for the next month. I personally like CUBE formulas for headline type reporting where I only want one value here and there and don't want to build a whole PivotTable to extract it.
@@MyOnlineTrainingHub Thanks Mynda will try it out
It flew over my head, but thank you
You're welcome, Hiruy. Better to be aware of CUBE formulas and not master them than to not know of them at all 😉
@@MyOnlineTrainingHub You're right. I'll learn it
Me Too..I am her viewer and fan since long time. She is always on it. But I think it takes few more videos to master the concept. I am PQ user since it was new.
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 ?
The easiest way to write the CUBE function is to build a PivotTable that displays what you want and then convert it to CUBE formulas.
Do we need make each and every cell independent in same way. It will be lot of manual effort. Is there any easy way to make independent all of the cells ?
Is there a way to generate the Category and Subcategory also from the cube so that they are dynamic too? In my case, my slicer has Project - so, when I click on it, the Category and Subcategory list gets changed, hence all the values become empty. How do I make that dynamic?
Nice I was always wondering how to use this feature. It might be useful to build some static reports, however I find the biggest drawback vs PowerPivot is that the data is no longer "in one piece" if you know what I mean. Still, for static reports, this may be very useful.
You can always load a single table to Power Pivot. It doesn't have to be split across multiple tables to work.
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...
Glad you liked it, Nicolas! You can’t double click drill down on a cube formula though.
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?
Hi Tunde, there aren't PivotTables in Power BI in the same degree that we have them in Excel, but there are matrix tables in Power BI which are similar. You can't use CUBE formulas in Power BI, so not sure what you were hoping to do there.
@@MyOnlineTrainingHub Thx for taking the time to reply, I just need to take the bull by the horns, copy my data in look into power bi and try matrix tables along with the other vizulisations 😆
Hi Mynda, thanks for this sharing. however, my slicer disconnected after the conversion. I'm not sure why.
thanks
My pleasure! Not sure why your slicer would disconnect. Try checking the slicer name to use in formulas matches the CUBE formula reference.
@@MyOnlineTrainingHub perhaps due to my slicer is set by monthly instead of yearly?
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!
Thanks, Parahi! I don't tend to use CUBE functions for big reports, so haven't come across this, but I can imagine they can get out of hand.
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
I haven't speed tested them, but my guess is CUBE formulas will be slower.
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.
OLAP cubes require the calculated fields to be added in the cube, not in Excel.
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?
No, the Slicer can only control PivotTables that share the same data mode/Pivot cache.
@@MyOnlineTrainingHub would a data validated list filter pivots?
No, data validation can't filter PivotTables or CUBE functions. You'd have to create your reports with SUMIFS etc. It'd be best to add your other data to the data model and create a relationship to the Slicers through dimension tables. You can learn more about this in my Power Pivot & DAX course: www.myonlinetraininghub.com/power-pivot-course
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]")
So pleased you're excited about CUBE functions, Emre! the measure should read [Sum of Amount]
Yes, aware of these, however for some reasons haven't found a lot of use for them.
Yeah, they're not for everyone.
Mynda, thank you for the glorious video. Do you know if the checkbox add data to data model can be automatically ticked on?
Thanks, Tina! There’s no option to default to data model AFAIK, but you could use Power Query to import the data and in there is a default load option. However, it’s probably overkill if you’re just getting data from the worksheet and not an external source.
@@MyOnlineTrainingHub thank you!!! I will follow up- i work with data from multiple workbooks, and i do a lot of different pivot tables in many additional sheets. My final workbook with the query and pivot tables is i believe overloaded. Can you please suggest how to continue so it does not take a lot of memory. Basically, I am using excel as a small costs database.
Perhaps separate your data workbook and your analysis workbooks. Use Power Query to get the data from your data workbooks and bring it into another file for your analysis.
@@MyOnlineTrainingHub that is really good tip, actually. I can bring the data into the another workbook w power query data model option, so it will not take that much space. Thank you a lot
@@MyOnlineTrainingHub last question, I swear. Do you see another better way of doing this, other than the one you described?
Hi Mynda, i wonder if i could bother with the following question?
Do u have a video/tutorial that explains the possibility of ‘importing’ an Excel power pivot model into Power Bi, but with the possibility of updating my original Excel workbook and ‘push through’ to Power Bi the periodic updates that i edit in the original Excel wirkbook?
Thanks
Martin ( South Africa)
Hi Martin, I don't have a UA-cam tutorial, but cover this in my Power BI course: www.myonlinetraininghub.com/power-bi-course Via the File tab in Excel > Publish > Export Workbook data to Power BI will do what you want. You may need an on-premises gateway to refresh, depending on your source data location.
@@MyOnlineTrainingHub thAnks mynda
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!
You need to use the CUBESET function with CUBERANKEDMEMBER for this, which I cover in my Power Pivot & DAX course: www.myonlinetraininghub.com/power-pivot-course
@@MyOnlineTrainingHub Yes indeed. I found that out myself, yesterday.
I find it rather convoluted (…).
My first solution was to use a DAX measure to CONCATENATEX all the unique values together with a “,” (and sort then, of course).
Then in Excel simply call that measure as a VALUE and TEXTSPLIT it. Honestly that is way easier, IMO. Also, that total-on-top is not issue this way, and it can always be VSTACKed at the bottom (or HSTACK as the case may be).
In general I find CUBE functions to be rather clunky to use and the fact that they do not uniformly vectorize in their scalar arguments doesn’t make it any easier. Then again it is a structured way of accessing the data model… :-)
This is only for static data? If I update tables this wont 'refresh' like a pivot table?
Yes it will. If you update the source data for Power Pivot you can refresh the connection and PivotTables and it will feed through to the CUBE functions. Ideally, you should use Power Query to get the data to load to Power Pivot/Data Model which the PivotTables are built on.
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...
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub noted with thanks...😆
What is the difference between this & sumifs function? Thanks in advance.
SUMIFS cannot reference data in the Power Pivot data model and CUBE functions cannot reference data in the Excel worksheet.
Mynda, can you please tell me why? when I convert the pivot table column headers not getting converted to cube member or whatever it's called. Because of this my slicers not functioning
Hi Anuri, I'm not sure what's causing this. Are you certain the column headers you're referring to are actually PivotTable fields and not manually entered headers? If you'd like me to take a look, post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub yes they are pivot table column headers. I think reason could be bacause of drop down arrows in the column headers . Can you please tell me how to remove the drop down arrows. I tried from pivot options by unticking display feild captions and filter drop downs but then it hides everything
The drop down arrows won't be causing the problem as these are the default and were in my PivotTable too.
3:15 shouldn't it be For *Charity Category* AND Donation Sub-category, For January 2021?
Charity is a sub-category of donation, so it goes without saying. i.e. donation is not a sub-category of any other category. It's a one for one relationship.
Can CubeValue functions also be referenced to Dynamic Arrays?
Yes, formulas containing CUBE functions can be referenced by other formulas using dynamic arrays.
@@MyOnlineTrainingHub CUBE FUNCTION DO NOT WORK WITH #
Just to be sure, the data must start from a Power Pivot table?
Correct.
Hi Mam,
I want to learn MS Excel
Great to hear, Rishikesh! There is a beginners playlist here: ua-cam.com/video/MnGIaqpqLTU/v-deo.html
Hi ...
How to convert cube function back into pivot table ?
Not possible, sorry.
Why might Olap tools be greyed out?
If you haven't added the data to the data model when creating the PivotTable.
Great
Glad you liked it, Mohit!
Hi Mynda, please advise if this automatically updates just like PivotTable?
For example I go into a new month (2022 May) will it automatically add the new month or do I have to add this manually by following the formula?
Another question is, I tried putting this into a table (Insert > Table), and my Cube calculations become #N/A but the formula is exactly the same, do you know why this happened?
Thank you!
Hi Eric, you would need to add new formulas for May and AFAIK CUBE formulas don't work in tables.
@@MyOnlineTrainingHub Thank you :)
Is it possible to insert a line ?
Not sure what you mean by 'insert a line'? You can insert rows and manually add formulas, if that's what you're referring to.
Yes - i meant a row! Thank you for your answer👌
BUT... you cannot drill down. That is where PivotTables will always win.
You got me, Dayve! That is the only thing they can't do 😉
Just use power BI :)
HI Jon what's your use case? I build Angular web apps that achieve more than Power BI for most use cases, but love excel for fast data analysis. Never found the sweet spot to get heavily into Power BI does it benefit you?
Using Power BI would be even more inflexible...the idea is to have the data in Excel in any format you want. Don't get my wrong, I love Power BI too. I cover the pros and cons of Excel vs Power BI here: ua-cam.com/video/0nAQ7gPO_6Y/v-deo.html
@@al3xj that’s cool Alex, I’ve been trying to lean angular. Power BI’s visuals are much easier to use and more appealing than excel, and it’s free on desktop if you’re just trying to create visuals and share them in the same way they are showing here in excel
@@MyOnlineTrainingHub what your category or subcategory changes? It seems like you’re coded a static number of items for each, or am I misunderstanding how this works?
@@MyOnlineTrainingHub can you explain what can’t be done in power bi? I’m not following. It looks like a matrix visual. Is it that in theory you could source the data from multiple cube models in any cell you want?
pretty confusing I have heard for the first time on this topic, that's a mistake to address such ambiguous topics without any prerequisites and elementary points
There is an element of assumed knowledge in videos on advanced topics, but if you want clarification on anything I’m happy to help if you email me.
Microsoft does a poor job promoting/advertising new features like these
Yeah, back then they did. I think they're getting better with new features these days.
Way to complicated in my opinion. Excel should be more user friendly
You can always stick with PivotTables, Christina 😊 Can't get much easier than having Excel write all the formulas for you in the PivotTable background.
This video is terrible. You didn't say when you change the sheet in 0:36. It took me ages to figure that out. OFC when I changed the sheet I have no access to the 'design' or 'analyse ribbon', bc you didn't explain how to do that. So I opened another pivot table, but OLAP tools icon is unavailable. You just waste my time. Please record another video, were you teach not waste people's time. Or just delete this video.
also you lie on your website. Your wrote 'To convert a PivotTable, select any cell in the PivotTable > then on the PivotTable Tools: Analyze tab > OLAP tools > Convert to Formulas'.
Which is a lie. OLAP tools are grey, unclickable
If your OLAP tools are grey then you haven't loaded the data to the data model (Power Pivot), you must be working with a regular PivotTable. I state this at 0:21.
@@kozlo1who hurt you? So rude to someone who provides free content to you.
Hi Mynda. Very interesting to make pivot tables dynamic. I just have a problem to make a date column dynamic. Can I ask you for help? French version. =MEMBRECUBE("ThisWorkbookDataModel";"[Relevés_St_Marc].[Date].&[2022-10-01T00:00:00]"). Thanks in advance.
Hi Alain, Please post your question on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
ty
You're welcome!