Pivot Table - Field Setting - Show items with no Data - Greyed Out

Question

Using Excel Home and Student 21, Version 2307

I am trying to create 2 Pivot Tables where the row labels at in sinc. The data source is the same for both the table. However, one segment is operational in all cities while the other one is operational in a few. So when I create a Pivot for both segments, one pivot comes out with all the cities and the other has a few cities only. I am trying to select "Show items with no Data" from the field option, but the same is grayed out. Below is the screenshot of the tables and the pivot option window. Please could you check and advise if and how this can be fixed?

The Pivot Data set Below

The Field Settings window below, 'Show items...' option greyed out

Answer 1

You created a PT with Power Pivot. Then that particular option is indeed greyed out. But you can achieve it under Options on the 'PivotTable Analyze' tab

In the 'Display' tab in the following window, check the box for 'Show items with no data on rows'.

Alternatively, create a measure that sums the values displayed. Let's say that measure is called 'Total Sales'. Then the measure to be used in the PT could be:

=IF( ISBLANK ([Total Sales]), 0, [Total Sales])

This will display 0 (zero) for all States with no sales.

Answer 2

I found what was causing the issue for me. Short answer: Data Models.

Explanation: When creating the pivot table, the "add this date to the Data Model" was automatically checked for me. I simply clicked to not include to the data model and my issue was resolved.

I now have the option to show items with no data on my pivot table.

To prevent excel from automatically checking the data models button, I would follow this article: An issue with the Data Model is preventing Microsoft Excel from - Microsoft Community. It shows how to turn that feature off. Mine was previously off - I'm unsure why it defaulted to being on again.

I hope this helps!