Pivot Tables, Calculated

Topic

New Reply

“By default, a PivotTable does NOT display items in a field that have no associated data.” This is word for word from Excel help. My problem is that when you have a field up in the “Page” function of the pivot table, AND you have a “Calculated Item” (such as the difference between budget and actual)it DOES display items in a field that have no associated data. I do not want to see zero’s for data that does not exist for a department I selected using the “Page” function on the pivot table. Please see the the file attached for the 2 examples of pivot tables… one with a calculated item, and one without. Thank you!

Reply | Quote

Replies

Go to Pivot Table Field Advanced Options and set AutoShow options to Automatic. I think that may sort your problem out.

regards,

Andrew

Reply | Quote

I tried the field, advanced, autoshow options, and changed it to automatic and the blanks did not go away. Did it work for you on the file I attached? Any other ideas??? I’m desperate to get this to work… no one wants to look at a lot of unnessary words, blanks, and zeros when they are viewing the comparisons of actual to budget. You know how it is.
Thanks,
LJM

Reply | Quote

It seemed to work, but on further examination it does not.

I think your problem has something do with the way the data is structured. Each row does not represent a record as such and this is probably causing some confusion to Excel.

Sorry about that. I’ll take another look and see if I can think of anything else. Hopefully some of the real brains in the lounge are trying too.

Reply | Quote

Sorry but could you please ignore and forgive my previous 2 replies – I was somehow looking at the wrong spreadsheet, which had nothing to do with your situation.

As far as your problem is concerned, the fact that a calculation takes place means that there is Data and hence the Item is shown. I tried a conditional sum but Excel was having none of it.

Thes idea I have for the moment is to peform the calculation outside of the Pivot Table. I am attaching a copy of your sheet with an example of what I mean. Its not perfect but the best I can do for now.

regards and apologies once again – its getting late here and thats my excuse.

Andrew

Reply | Quote

Another approach is to do the Variance calculation before the Pivot Table and include it as a “Source” field.

Sample attached. It might be better than the last approach aesthetically but requires more work !

Andrew

Reply | Quote

I did not see where you had defined the Variance as a calculated item so I did another version of your Pivot table. The secret to hiding the zeros is to double click on the Expense Type and Select Aedvanced and then limit the display to the top 5-fields. See attached.

Reply | Quote

This solution occurred to me also, but I think it contains a flaw.

If you select ALL departments in the Page Field you still get only the top 5. That is fine if the top 5 is what you want, but what happens if another variance evaluates to 0 due to updated data.

The problem arises due the fact a calculated field returns data, even it is null it is still data and hence the “Show Fields With No Data” does not apply.

I am attaching a file so you can see the different approaches I took.

Look at sheets Report B and Data C to see what I mean.

Andrew

Reply | Quote