Show Items with no Data in PivotTables

Show Items with no Data in PivotTables is a handy setting that allows you to show items in the PivotTable even if there isn’t any data for them. For example, the PivotTable and chart below includes the East and West regions even though the Sum of Count is blank:

Watch the Video

Download Workbook

Show Items with no Data in regular PivotTables

With regular PivotTables we can easily show items with no data in the Field Settings > Layout & Print Tab:

Show Items with no Data in Power Pivot PivotTables

However, in Power Pivot the show items with no data check box is greyed out:

UPDATE: In some versions of Excel you will be able to skip the custom measure step below and instead check the boxes in the PivotTable Options Display tab for 'Show items with no data on rows/columns' as shown below (note: you will still need the dimension table and relationship explained below):

Dimension Table

To support the measure or the settings in the PivotTable Options if you have them, you must add a table to your Power Pivot model that contains a distinct list of the items you want displayed. This is called a dimension table (see video for detailed steps).

Then you need to create a relationship between your source data table and the dimension table.

Now you can go to PivotTable Options and check the 'show items with no data on rows/columns' in the display tab. However, if you don't have the PivotTable Options settings, then you need to create a measure that returns a count of zero or blank where there is no data, which forces the items to display:

Show Items With No Data Measure

The measure simply checks if the sum of the count column is blank, if so, it returns a zero, otherwise it returns the sum of the count column. This forces every region to be displayed irrespective of the count.