Add a custom on column to Pivot Table

[T_Bos]

Hello Community,

I have an annoying issue that I need help with. From the table below, I want to check a column I and if there is no Late record, I want to add a column to my pivot table that shows 0 for each month. How can I achieve this?

 

[GraH]

Maybe you can fool your pivot like this.

1. In the settings choose "Max" from the drop down to retain items deleted from the data source.
   
   
   
2. While you are in the settings, on the Layout & Format tab, check this option
   
   
   
   
3. And set the field settings to show items with no data
   
   
   
4. Then add a dummy line to your data, including a "late" label.
5. Refresh your pivot.
6. Delete the dummy line.
7. Refresh again.
   

 

[T_Bos]

Maybe you can fool your pivot like this.

1. In the settings choose "Max" from the drop down to retain items deleted from the data source.
   
   View attachment 28935
2. While you are in the settings, on the Layout & Format tab, check this option
   View attachment 28938
   
3. And set the field settings to show items with no data
   
   View attachment 28936
4. Then add a dummy line to your data, including a "late" label.
5. Refresh your pivot.
6. Delete the dummy line.
7. Refresh again.

Thank you very much. This did the trick but it creates a blank column heading and I can't seem to change the name to "Late." It also creates a row name blank that I cant seem to delete. See below.

 

[T_Bos]

Maybe you can fool your pivot like this.

1. In the settings choose "Max" from the drop down to retain items deleted from the data source.
   
   View attachment 28935
2. While you are in the settings, on the Layout & Format tab, check this option
   View attachment 28938
   
3. And set the field settings to show items with no data
   
   View attachment 28936
4. Then add a dummy line to your data, including a "late" label.
5. Refresh your pivot.
6. Delete the dummy line.
7. Refresh again.

I tried a different way. One thing I noticed is that the "Show items with no data" is greyed out and I can't make a selection. The Late column disappears when I delete the dummy line but you can see the label in the field list.

After creating the dummy line and refresh:

After deleting the dummy line and refresh:

 

[GraH]

Hi again,

It does work, but you need to follow exactly as I pointed out
- Items with no data is on the field settings, not the pivot settings
- (Blank), you can either filter out as it is the result of the fake row or change existing rows and after the refresh change them back and refresh again

Alternative is
- converting the range to a table
- then create the pivot
- change/add rows to have all labels
- refresh the pivot
- Change back/delete the "dummy" rows
- refresh again

 

[T_Bos]

Hi again,

It does work, but you need to follow exactly as I pointed out
- Items with no data is on the field settings, not the pivot settings
- (Blank), you can either filter out as it is the result of the fake row or change existing rows and after the refresh change them back and refresh again
View attachment 28993

Alternative is
- converting the range to a table
- then create the pivot
- change/add rows to have all labels
- refresh the pivot
- Change back/delete the "dummy" rows
- refresh again

View attachment 28994

Super! It finally worked for me. Thank you very much for this trick.

 

[GraH]

Glad to read it works out. Thanks for the feedback.