When I found pivot tables (circa 2007) and learned how to add calculated fields to them (i.e. to calculate weighted averages) I was astonished how powerful they are. Recently I learned that you can code your own “pivot tables” with more flexible formatting using CUBE functions.
XLookup and cube functions both allow the developer to set up multiple fields as filter and aggregation parameters to provide an interactive experience that can be used to compress many permutations of the data into one view.
You can build dynamic pick-lists and interactive charts with them. You can combine data from any table in your data model and display fields at different aggregation levels — Sales Rep compared to the others in the same territory or product category.
When I learned about CUBE functions the first time I didn’t have a use case for building a complex dashboard in Excel so I sort of ignored them. Now I have a use for them and I need to be careful not to over use them because they are somewhat slow to calculate.
Maven Analytics has a good course to learn them and then I got a recommendation from George Mount to watch a training from Brent Allen that taught me a quick way to get the formulas partially written for you!
You can create a pivot table using your data model and then in the OLAP Tools “Covert to Formulas”.
Use cases:
- Deduplicate values in a dynamic dataset to provide a picklist
- Dynamically rank values
Carragee Consulting 
Leave a comment