Show items with no data on pivot table.

I am using a pivot table from PowerPivot data and I have a field called 'Sections' in the pivot table Columns that I need to show all of the sections even if there is no data after I select dates with a slicer. There is a button 'Show items with no data' under Field Settings but it is disabled. Is this because I am using PowerPivot? Is there some other way to show all the Sections in this Pivot Table? Driving me crazy here.
Thanks.

I was able accomplish something similar by creating a separate table containing a unique list of the values I wanted to remain static in my pivot table structure. In this case, it would be a unique list of 'Sections'. Once you create the list as a separate table, add it to your data model and link the 'Sections' field from the original Data table to the 'Sections' field in your new table. After you refresh, you should see both tables in your pivot table field list. You will use the 'Sections' from the newly created list and the values from the original data table.

However, you will also need to create a calculated field to show 0 for blanks... in my case it was pretty simple as I only needed the sum of one variable, but I am not sure how complex your pivot table is.

Here's the expression I used:

=IF(ISBLANK(SUM(DataTableName[VARIABLE])), 0, SUM(DataTableName[VARIABLE]))

Hopefully that helps... it took me a while troubleshooting this before I was able to finally get the result I was looking for, but it eventually worked for me.

Hi,

I have almost exactly the same problem, but from what I understand of this solution, it won't work for me. I have a huge data table where I'm trying to get a count of unique values (which are not numbers, so a calculated field won't work here), and I have a bunch of filters that I change frequently. Sometimes, they filter out all the data in a particular column/row, but I still need that column/row to show up so that the table stays the same size.

I have gone into Pivot Table Options > Display > Show items with no data on rows/columns, and those are both checked, but the rows/columns are still missing.

I have gone into Field Settings > Layout & Print, but the "Show items with no data" option is grayed out.

All the other places I've looked say to try the second option, but apparently that's not available to me. Does anyone know why, or why the first option isn't working? What else can I do?

Hi All.

My resolution was to create a pivot table without "Add this data to data model" checked in. Worked 100% after it.

Cheers,
Valentyn

Hi All,

I'm hoping that my response will come out as intended, as it's not always easy to understand what another person means.


As per my example, I clicked on the Pivot's Columns "Ageing"
Pivot Column.PNG

Then click "Field Settings", Layout & Print and check "Show items with no data".
ShowItemsWithNoData.PNG

Click "OK"

Hope it works for someone else too.

I was intimidated by the more complex solution I found earlier - this one is simple and worked great for me, thanks!