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.
@@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…
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]”
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
![Excel GetPivotData Function | Udemy Instructor, Leila Gharani [bestseller]](http://i.ytimg.com/vi/Bvv8o3YMB8Y/mqdefault.jpg)
![[Full] 4 ต่อ 4 Celebrity EP.922 | 10 พ.ย. 67 | one31](http://i.ytimg.com/vi/zmV5HRIxIBI/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.
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!
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.
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.
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.
Gotta love the “it depends”
Yes, it always depends. 😀
You can’t provide a solution unless you know the problem.
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.
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.
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.
Awesome👍 need more automate excel vedio
Wow, Thank you so much, I learnt a lot.
Good stuff… those CUBE functions are super useful.
very useful, thank you
You are welcome!
Great presentation!
Much appreciated!👍
Thanks Muuip.
This was super helpful.. Thank you!!
Thank you 👍
Best excel videos seen
Thanks Nigel 👍
Nicely done. Thanks!
Thanks Bill 😊
Excellent, thank you
Great content. Thanks for sharing.
You’re welcome. 😀
Good stuff man
You're welcome.
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…
Awesome Mark
Glad you enjoyed it 👍
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?
PL videos on DAX functions.thank yu
You are good
Thanks 😊
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]”
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
Rarely i create pivots on new sheet.
Well explained, thanks!
Thanks, I’m glad you found it useful.