• Pivot Tables, Calculated

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Pivot Tables, Calculated

    Author
    Topic
    WSLJM
    AskWoody Lounger
    February 19, 2001 at 9:30 pm #353077

    “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
    Viewing 3 reply threads
    Author
    Replies
    • WSAndrew Cronnolly
      AskWoody Lounger
      February 19, 2001 at 10:09 pm #515412

      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
      • WSLJM
        AskWoody Lounger
        February 19, 2001 at 10:23 pm #515414

        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
        • WSAndrew Cronnolly
          AskWoody Lounger
          February 19, 2001 at 10:53 pm #515417

          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
    • WSAndrew Cronnolly
      AskWoody Lounger
      February 20, 2001 at 12:58 am #515419

      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
    • WSAndrew Cronnolly
      AskWoody Lounger
      February 20, 2001 at 1:20 am #515420

      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
    • WSepic60sman
      AskWoody Lounger
      February 26, 2001 at 3:01 am #516293

      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
      • WSAndrew Cronnolly
        AskWoody Lounger
        February 26, 2001 at 8:54 am #516314

        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
    Viewing 3 reply threads
    Reply To: Pivot Tables, Calculated

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    June 2025
    S M T W T F S
    1234567
    891011121314
    15161718192021
    22232425262728
    2930  

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.