Power Pivots and the Data Model take pivot tables to another level or two, but there are a few limitations you need to be aware when you use Excel’s data model to create a pivot table.

If you have been using pivot tables for a while you may have used some of the more advanced features. Some of these are not available in Power Pivot pivot tables.

These limitations are not all that radical and the abilities you gain more than offset the limitations. In  many cases there are workarounds and even improvements.

No grouping

The ability to group dates and other codes is disabled in the Power Pivot pivot tables. If grouping is required you could add a field to the data table that could be used as a group. 

No custom lists

In traditional pivot tables you can use a custom list to sort items in the reports. This is not possible in Power Pivot pivot tables. If you don’t know what a custom list is, check out this blog post.

Display detail

A pivot table feature that many people find by accident is the ability to double click a value cell in a pivot table. When you do, it creates a new sheet with all the data that makes up value that you double clicked. This feature is limited to 1000 rows in a Power Pivot pivot table – probably because they typically work with large data sets.

No Calculated Fields or Items

Not many people realise you can perform calculations in a traditional pivot table. These calculations are called Calculated Fields and Calculated Items. These are great for % calculations within the pivot table. These calculations are not possible within a Power Pivot pivot table. This limitation isn’t really a limitation. Whilst you lose these calculations you gain the DAX function language. This is more than a fair swap as DAX is extremely powerful. Anything you do with Calculated Fields or Calculated Items can be done with DAX formulas.

So whilst there are some things you can’t do in a Power Pivot pivot table, the benefits far outweigh the limitations. Just DAX is reason enough to the use Power Picot and the data model.