Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Pivot Tables, Calculated

    "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!
    Attached Files Attached Files

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Pivot Tables, Calculated

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

    regards,

    Andrew

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Pivot Tables, Calculated

    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

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Pivot Tables, Calculated

    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.

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Pivot Tables, Calculated

    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
    Attached Files Attached Files

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Pivot Tables, Calculated

    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
    Attached Files Attached Files

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Tables, Calculated

    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.
    Attached Files Attached Files

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Pivot Tables, Calculated

    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
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •