The fact that you explained the three main options available for retrieving information from a PivotTable, made the CUBE functions much easier to understand. Thanks so much Mark.
Hi Mark. Very helpful! GETPIVOTDATA and CUBE functions seem complicated at first, but with a little effort, they become more understandable. Thanks for demonstrating. Thumbs up!!
The question I had was best option between GetPivotData and Cube tables. For a large FP&A workbook, I can either create a data model / power pivot table reference and link to it via formulas or try implement a cube table instead. What are the pros and cons to each approach? I can make my static outputs dynamic with drop downs/getpivotdata without needing slicers so what advantages do cube functions give me? Will cube tables affect performance speed?
Power Pivot and CUBE formulas are superior to standard PivotTables. Only use a standard PivotTable for simple one-off analysis. But anything you care about you want to use Power Pivot. The file sizes will be smaller and the calculation engine is more flexible as you can use DAX formulas. However CUBE formulas are single threaded, so might become slower if you use a huge number of them. CUBE formulas can connect directly to a published Power BI dataset, so data doesn’t need to be in Excel. You can use a Slicer directly in a CUBE formula, so there is no issues with that.
I often use GETPIVOTDATA and from pivot tables generated from the data model. The syntax for referencing fields within the GETPIVOTDATA function then changes. Recommend covering in a future video the format of the argument referencing in this scenario.
You can set it as a default for your PC on Excel 2021 and Excel 365. File > Options > Data > Edit Default Layout... > PivotTable Options... Then uncheck AutoFit column widths on update
I have the same question as Brian. I my case I want to look up a person's phone number and email address in a table in the 'data model' in Excel (referencing off a customer number). The =CUBE functions only seem capable of counting phone numbers the customer has. (I get a "1" returned instead of the phone number itself.) There appears to be no =CUBE function capable of this. So instead I must load the data to an ordinary table (instead of to the data model) and use =VLOOKUP. Adding a table to a new sheet is suboptimal. If =CUBE formulas cannot pull a data item from the data model in Excel, what formula should Brian and I use?
Can we define a name pointing to a column of a table existing in a data model ? Like we would refer a column table existing in a worksheet : “My_Table[Column_A]”
@@ExcelOffTheGrid Mark, if you know of a PQ connector that can extract data from the data model inside an Excel file, then make that a much higher priority. (I don’t think it exists, though) Anyhow, that’s the wider context to that question. Also, check out EMT 1435 by excelisfun. It gives another perspective on how to access the data model…
![Excel GetPivotData Function | Udemy Instructor, Leila Gharani [bestseller]](http://i.ytimg.com/vi/Bvv8o3YMB8Y/mqdefault.jpg)
The fact that you explained the three main options available for retrieving information from a PivotTable, made the CUBE functions much easier to understand. Thanks so much Mark.
It can get a little complicated, so I’m glad it’s all a bit clearer.
Hi Mark. Very helpful! GETPIVOTDATA and CUBE functions seem complicated at first, but with a little effort, they become more understandable. Thanks for demonstrating. Thumbs up!!
Very true Wayne. A bit of practice, it starts to make sense.
Very helpful comments on the syntax and making it more independent from the table format. It's something practical that I can use right now.
Thanks, I’m glad it was helpful.
The cubevalue was an eye opener for me. Thanks a lot
You are welcome!
Thanks Mark. That Cube function is new to me and really mind-blowing. Your videos are all excellent and well explained. Thank you.👍👍
CUBE functions are amazing. They even let you get calculated values out of a Power BI data model.
Cube function isn't known and very magic formula to evaluate data.
Thank you so much this good video
Cube functions are some of my favourites. But it normally takes a lots of work (i.e. a data model + DAX) before they can be put to good use.
I think Cube is easier than GetPivot. You can get started by converting pivot to OLAP and then the syntax is quite simple from there.
The question I had was best option between GetPivotData and Cube tables. For a large FP&A workbook, I can either create a data model / power pivot table reference and link to it via formulas or try implement a cube table instead. What are the pros and cons to each approach? I can make my static outputs dynamic with drop downs/getpivotdata without needing slicers so what advantages do cube functions give me? Will cube tables affect performance speed?
Power Pivot and CUBE formulas are superior to standard PivotTables.
Only use a standard PivotTable for simple one-off analysis. But anything you care about you want to use Power Pivot. The file sizes will be smaller and the calculation engine is more flexible as you can use DAX formulas.
However CUBE formulas are single threaded, so might become slower if you use a huge number of them.
CUBE formulas can connect directly to a published Power BI dataset, so data doesn’t need to be in Excel.
You can use a Slicer directly in a CUBE formula, so there is no issues with that.
Best excel videos seen
Thanks Nigel 👍
Great presentation!
Much appreciated!👍
Thanks Muuip.
Awesome👍 need more automate excel vedio
Wow, Thank you so much, I learnt a lot.
Good stuff… those CUBE functions are super useful.
This was super helpful.. Thank you!!
Thank you 👍
Gotta love the “it depends”
Yes, it always depends. 😀
You can’t provide a solution unless you know the problem.
Nicely done. Thanks!
Thanks Bill 😊
very useful, thank you
You are welcome!
I often use GETPIVOTDATA and from pivot tables generated from the data model. The syntax for referencing fields within the GETPIVOTDATA function then changes. Recommend covering in a future video the format of the argument referencing in this scenario.
Great content. Thanks for sharing.
You’re welcome. 😀
Excellent, thank you
Awesome Mark
Glad you enjoyed it 👍
PL videos on DAX functions.thank yu
Good stuff man
You're welcome.
Thanks I finally get what cubemember is for.
Can you do how slicer works with cubevalue? I got it working but not understand how and why.
You are good
Thanks 😊
Pivot AutoFit should be a Regional Setting you can turn off. Very frustrating to have to do this every single time I create a pivot.
You can set it as a default for your PC on Excel 2021 and Excel 365.
File > Options > Data > Edit Default Layout... > PivotTable Options... Then uncheck AutoFit column widths on update
Hello, how I can get the excel sheet please?
If I have a product table, can cube functions pull the product description from the same table using the product number as a lookup?
I have the same question as Brian. I my case I want to look up a person's phone number and email address in a table in the 'data model' in Excel (referencing off a customer number). The =CUBE functions only seem capable of counting phone numbers the customer has. (I get a "1" returned instead of the phone number itself.) There appears to be no =CUBE function capable of this. So instead I must load the data to an ordinary table (instead of to the data model) and use =VLOOKUP. Adding a table to a new sheet is suboptimal.
If =CUBE formulas cannot pull a data item from the data model in Excel, what formula should Brian and I use?
Can we define a name pointing to a column of a table existing in a data model ? Like we would refer a column table existing in a worksheet : “My_Table[Column_A]”
Hi Mark,
Can you show us how to use CUBE functions to extract from the data model a list of unique row headers like we need in a pivot table?
Thanks.
This has been on my list for a long time. Maybe I’ll bump it up the list a bit.
@@ExcelOffTheGrid Mark, if you know of a PQ connector that can extract data from the data model inside an Excel file, then make that a much higher priority. (I don’t think it exists, though) Anyhow, that’s the wider context to that question.
Also, check out EMT 1435 by excelisfun. It gives another perspective on how to access the data model…
Rarely i create pivots on new sheet.
Well explained, thanks!
Thanks, I’m glad you found it useful.