Show all rows with no data in Pivot table

[darian]

Hi experts,


My pivot table shows all my data till I add data into my column field or report filter, then it will only displays rows that have related data. If I change any column or row to "Show items with no Data" it repeats all the data in different categories.


P.S I have populated my data from access and I have also tried from a table inside excel... nothing has worked so far.


Please help.

https://drive.google.com/file/d/0B980etBxqQuzZ1hnZzNHcHZSNWM/edit?usp=sharing

Below I have attached the picture of my pivot table after adding filter and second table is before... So I basicly want the second table and if the filter doesn't relate to employee names, then shows 0.

 

[scottsen]

Just to un-confuse the terminology a bit, the table filtered down to 1 row when you added a measure to the VALUES area of the pivot table (apparently Count of Completion Date).

As you have found, excel filters out rows that have all values equal to blank. So... do you plan to show any "values" in the pivot table? If those calculation return "0", and not BLANK(), you would like get what you want.

 

[darian]

Thank you Scottsen,

as you can see in the picture in the first table, the problem starts when I add asset title to my columns and it immediately filter out my employees name that don't have any related data to the asset title.
but if I remove my filter as you see in my second table in picture, all employee names pops up and adding count completion doesn't really do anything with filtering my data.

 

[scottsen]

Any chance you can send a link (dropbox, google drive) to your workbook?

 

[darian]

Hi scottsen,

Thank you for your help. I have PMed you my db and excel file for your reference.

I would like to be able to see all users who is registered or not, who has taken the course or not... so all names from any location and any role who is on the file would like to be seen, but those who have completed the filtered-course gets 1 and the rest get 0.

problem is here that when I filter the course, people who don't have related filtered-course disappeared, but I wanna see them just in case if they are not being registered or not taken the course in system get fixed.

Thank you very much for your help

Darian

 

[scottsen]

Ah! You were sneakier than most. Usually I get a whiff of this earlier

You are in the Power Pivot forum. What is PowerPivot? « PowerPivotPro

I am not a normal old pivot table ninja, I suspect the people in the "main" excel forum can help you easier than I can. If you decide that power pivot looks interesting, certainly come back...

 

[darian]

scottsen, can you tell me what is the difference and what is the advantage of powerpivot?

Thank you

 

[scottsen]

Please see the blog I linked in my previous reply, and the sticky at the top of the forum. Those can answer way better than me