• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pivot Table options are grayed out

C

colans

New Member
I have several pivot tables with with either products or customers in rows and months in columns. Outside of the table, I have various calculations, 12 month trend, 3 month trend, current month compared to trends etc.
If I filter my row to a certain product that does not have data in all columns (months) the calculations outside the table to not work.
I went to PivotTable Options / Display and the "Show Items with No Data on Rows and Columns" are both grayed out. Why and can I get this function to work?
I tried to attach a small file but it's saying it's too large to attach, sorry.
 
colans, are you using Excel 2013/2016/365? Are you using a data model? Then you are possibly using Power Pivot. The features are different from standard pivot. I'm not sure, but the option "Show Items with No Data on Rows and Columns" does sound like "not very useful with Power Pivot".
 
Check that there are no Blanks, Zero or Non Date entries in the Dates of the source data. Even check that the dates are Numbers and not text
If there is remove or correct them
 
GraH - Guido said:
colans, are you using Excel 2013/2016/365? Are you using a data model? Then you are possibly using Power Pivot. The features are different from standard pivot. I'm not sure, but the option "Show Items with No Data on Rows and Columns" does sound like "not very useful with Power Pivot".
Click to expand...

I am using 2016. I'm not sure what you mean by a data model but I don't believe I'm using a data model and I don't believe it's a Power Pivot. Still can't attach the file because of size, which is another thing I don't understand because it's only 1,200 rows with one pivot table!
 
Hui said:
Check that there are no Blanks, Zero or Non Date entries in the Dates of the source data. Even check that the dates are Numbers and not text
If there is remove or correct them
Click to expand...
I checked the format of the dates, no blanks, no zeros. Still doesn't work.
 
colans said:
I am using 2016. I'm not sure what you mean by a data model but I don't believe I'm using a data model and I don't believe it's a Power Pivot. Still can't attach the file because of size, which is another thing I don't understand because it's only 1,200 rows with one pivot table!
Click to expand...
Can you look if this flag is set, please. If it is, try to uncheck it and see it the file size gets smaller. If this is grayed out, I'm sure you are in a power pivot and not in a "legacy" pivot.
upload_2018-2-8_17-34-19.png
versus
upload_2018-2-8_17-37-28.png
 
Unfortunately, that didn't make it smaller.
The section I am referring to is below: Show items w/no data on rows
Show items w/no data on columns
upload_2018-2-8_12-8-47.png
 

Attachments

  • upload_2018-2-8_12-6-7.png
    upload_2018-2-8_12-6-7.png
    153.3 KB · Views: 16
If you can, upload the workbook to DropBox or some other 3rd party file sharing site. We can grab it from there.
 
Ok, here's the file (there were hidden tabs that were making the file so big)!
If you filter the rows to just Brand 4, I no longer have a 3 month trend in column S. How to I keep all 13 months in the table?
 

Attachments

  • Pivot Table example.xlsx
    81.3 KB · Views: 23
Here you go.

You should right click on the field (in this case Invoice Month) and go to field options and check "Show items with no data".
upload_2018-2-8_15-22-35.png
 

Attachments

  • Pivot Table example.xlsx
    83.4 KB · Views: 35
Similar results can be attained using dimension table in PQ/data model as well.
Though that’s a bit more involved.
 
I am a OFFICE 365 user, I have notice the below highlight field is grey out. I don't have option to select that. Is there any setting to fix. please answer !!

74613
 
Hello everyone, seems that i have the same problem, on a tabular mode of a pivot linked with a data model in power pivot I can't display items without values in the rows.
I ve tried so far , the "isblank" solution in the measurements and the display options on table options, nether of this looks like working, any idea how we could work around that?
 
You must log in or register to reply here.
Back
Top