Pivot Table "Show items with no data" greyed out

So...it makes data models kind of useless since the table's range bounces all over depending on how you slice the data...that is unless you take the time to stuff in/and manage a bunch of useless data in the table just as place holders.  For instance, slicing tables that pivot on date ranges to develop graphical models is completely different if you don't have the option to show fields with no data.  Please help.  Is there a fix in the works?  

Thanks,

Josh

So...it makes data models kind of useless since the table's range bounces all over depending on how you slice the data...that is unless you take the time to stuff in/and manage a bunch of useless data in the table just as place holders.  For instance, slicing tables that pivot on date ranges to develop graphical models is completely different if you don't have the option to show fields with no data.  Please help.  Is there a fix in the works?  

Thanks,

Josh

When using the data model, you could use a separate date table which is linked to all of your tables in your data model.

The date table can be automatically created in PowerPivot (Design tab > Calendars group > Date Table > New) and includes each day from the first date of the first year in your data to the last date in the last year of your data. Your pivot tables should then use the dates from this "Calendar" table rather than the dates in their respective data sets.

When wanting to include rows with no data in this manner, one then needs to right click on the PivotTable field and select "PivotTable Options" > Display > Show items with no data on rows (as opposed to "Field Settings" as is done with standard PivotTables).

Hope this solves your problem.

Hi johndolan2,

We test in 4 different channels of Excel and get the same result, when creating pivot table, if we tick the checkbox "add this data to the data model",  the "show items with no data" will be greyed out in Field settings. So it seems an expected behavior in Excel. Unfortunately we haven't found any official article about this, appreciate your understanding.

One guess is that when we add the data to data model, if we want to display all items with not data, we need to ensure the data source has all items recorded/listed. For example, if there is a date column in source data, even there is a date without any data, we still need to leave that date field in the source. Then after we set "Show items with no data" for rows and columns in PivotTable Options, the pivot table will display all items with no data.

Besides, welcome community members who have similar experience share insights here.

Best Regards,

Tina

Hi Tina. Here they are.....

Thanks. John

Hi johndolan2,

Thanks for sharing your experience with us.

To better understand the situation, could you please help to confirm the following information?

1.Regarding "I have selected the pivot table options to "show items with no data" in columns and rows",

Do you right click the pivot table and select PivotTable Options>in Display tab, check "Show items with no data on rows" and "Show items with no data on columns"?

2.1)Please share a screenshot about the issue symptom from your side.

2)You mentioned "When creating my pivot table I am selecting   "add this data to the data model" . ",

if you create a simple new pivot table, select  "add this data to the Data Model") too,  is the option for "show items with no data" greyed out via Field settings>Layout & print option?

3.Please share a full screenshot of Product Information in Excel File>Account, we 'd like check if we can reproduce the same behavior.

Best Regards,

Tina