Excel: Feature X Won't Work in Power Pivot

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

This is not a regular pivot cache pivot table that you've been using for the last 10 years. That data in your Excel worksheets is now in the Data Model. Even though the Data Model is stored inside of your Excel file, it gets treated like external data.

A lot of features that you might love in regular pivot table are not available when the data is stored externally. This was particularly bad in version 1 of Power Pivot. The later versions of Power Pivot now in Excel 2010 and Excel 2013 have mitigated some of the problems. But, you will still run into problems:

  • You can not group data
  • Double-clicking to drill-down will only return 1000 rows
  • Pivot tables are not automatically sorted by custom list.
  • You can't use the Show Report Filter Pages command to replicate the pivot table for every customer.
  • The GETPIVOTDATA formula is wacky, which makes it significantly harder to parameterize your GETPIVOTDATA formulas. Where a regular pivot table would generate an argument pair of "œ"œ, the OLAP version of GETPIVOTDATA uses "œ[Sales].[Customer]","[Sales].[Customer].&[Astonishing Shoe Inc.]". This means that you have to concatenate "œ"œ"œ[Sales].[Customer]"",""[Sales].[Customer].&["œ before the customer name and then "œ]" after the customer name.

For more resources for Microsoft Excel