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.
@ExcelOffTheGrid2 жыл бұрын
It can get a little complicated, so I’m glad it’s all a bit clearer.
@MLFranklin Жыл бұрын
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.
@ExcelOffTheGrid Жыл бұрын
Thanks, I’m glad it was helpful.
@subbu_ca Жыл бұрын
The cubevalue was an eye opener for me. Thanks a lot
@ExcelOffTheGrid Жыл бұрын
You are welcome!
@wayneedmondson10652 жыл бұрын
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!!
@ExcelOffTheGrid2 жыл бұрын
Very true Wayne. A bit of practice, it starts to make sense.
@teoxengineer2 жыл бұрын
Cube function isn't known and very magic formula to evaluate data. Thank you so much this good video
@ExcelOffTheGrid2 жыл бұрын
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.
@davidferrick Жыл бұрын
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.
@kebincui Жыл бұрын
Thanks Mark. That Cube function is new to me and really mind-blowing. Your videos are all excellent and well explained. Thank you.👍👍
@ExcelOffTheGrid Жыл бұрын
CUBE functions are amazing. They even let you get calculated values out of a Power BI data model.
@maximumaverage Жыл бұрын
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?
@ExcelOffTheGrid Жыл бұрын
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.
@graemegourlay2850 Жыл бұрын
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.
@arokiarajan12302 жыл бұрын
Awesome👍 need more automate excel vedio
@superyngo7 ай бұрын
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.
@srizki2 жыл бұрын
Wow, Thank you so much, I learnt a lot.
@ExcelOffTheGrid2 жыл бұрын
Good stuff… those CUBE functions are super useful.
@Muuip2 жыл бұрын
Great presentation! Much appreciated!👍
@ExcelOffTheGrid2 жыл бұрын
Thanks Muuip.
@pancholitachiu232 жыл бұрын
Gotta love the “it depends”
@ExcelOffTheGrid2 жыл бұрын
Yes, it always depends. 😀 You can’t provide a solution unless you know the problem.
@iliesboukhatem7803 Жыл бұрын
very useful, thank you
@ExcelOffTheGrid Жыл бұрын
You are welcome!
@tahirhanif966911 ай бұрын
Excellent, thank you
@JuanNadal Жыл бұрын
This was super helpful.. Thank you!!
@ExcelOffTheGrid Жыл бұрын
Thank you 👍
@bbotzong2 жыл бұрын
Nicely done. Thanks!
@ExcelOffTheGrid2 жыл бұрын
Thanks Bill 😊
@database_tips_tricks2 жыл бұрын
Best excel videos seen
@ExcelOffTheGrid2 жыл бұрын
Thanks Nigel 👍
@FRANKWHITE19962 жыл бұрын
Great content. Thanks for sharing.
@ExcelOffTheGrid2 жыл бұрын
You’re welcome. 😀
@paulmfti2 жыл бұрын
Good stuff man
@ExcelOffTheGrid Жыл бұрын
You're welcome.
@KhurrumIqbal1 Жыл бұрын
You are good
@ExcelOffTheGrid Жыл бұрын
Thanks 😊
@ankitchopra12869 ай бұрын
Awesome Mark
@ExcelOffTheGrid9 ай бұрын
Glad you enjoyed it 👍
@GeertDelmulle Жыл бұрын
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.
@ExcelOffTheGrid Жыл бұрын
This has been on my list for a long time. Maybe I’ll bump it up the list a bit.
@GeertDelmulle Жыл бұрын
@@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…
@brianleewilliams2 жыл бұрын
If I have a product table, can cube functions pull the product description from the same table using the product number as a lookup?
@raymorin.tu017 ай бұрын
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?
@chandramohan14182 жыл бұрын
PL videos on DAX functions.thank yu
@frederic_millenial Жыл бұрын
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]”
@moribatraore3774 ай бұрын
Hello, how I can get the excel sheet please?
@davidferrick Жыл бұрын
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.
@ExcelOffTheGrid Жыл бұрын
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