10 differences between a PowerPivot Pivot Table and a Standard one

1) PowerPivot Pivot Tables allow you to mix and match data from different tables (provided relationships between them have been created in the data model)

2) Summary Functions:

PowerPivot Pivot Tables lack the “Product” and “Count Numbers” summary functions that standard pivot tables offer. But they do have the handy “Distinct Count” 🙂

3) Subtotals:

Switching the subtotals off in a PowerPivot pivot table will only apply to the visible fields. Should you add a new field, it will have its subtotals on. This will also be the case if you happen to remove a field and subsequently add it back on. Switching the subtotals off for a standard pivot table, on the other hand, will apply to all fields both visible and invisible. It will also not reset, should you decide to change your pivot table layout

4) Totals:

You can choose to include values for items that have been filtered out from the dataset. Super handy if you need to calculate % of all!

5) Calculated Fields:

You cannot add calculated fields to a PowerPivot Pivot Table. Any additional calculations that you may need should be added to the PowerPivot model with DAX

6) Grouping:

Similarly, you cannot group items – once again, you will need to use DAX in the PowerPivot data model

7) “Show Items with No Data” is disabled

8) The super handy shortcut of multiplicating a Pivot Table – Show Report Filter Pages” – no longer works

9) Double clicking on a cell in a standard Pivot Table will return all of its underlying data

(provided that the default setting of “Enable Show Details” has not been un-ticked)

Double-checking on a cell in a PowerPivot Pivot Table will only return the first 1000 rows of its underlying data (…and if you’re mixing and matching fields from multiple tables, double-clicking will only get the numeric fields from the table feeding the “Values” area)

10) Those of you who are used to creating their pivot tables with VBA – brace yourselves – a PowerPivot Pivot Table cannot be created with VBA, only modified

Have you discovered any other differences? I would love to hear!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.