Excel Pivot Table with a Calculated Item RRS feed

  • Question

  • I have a filtered Pivot Table.  The columns contain the correct data based on the filtered table.

    When I add a calculated item, then I get addition columns that really don't have data and I get a #DIV/0!.  I understand how to change the Pivot Table settings not to show this text.

    The issue is when I do the calculation, it adds column that really have NO value to the table  (all the data has 0 value).  Therefore, these columns shouldn't be displayed.

    How do I "filter" the data so I don't get these columns?



    Thursday, March 19, 2015 1:54 PM


  • 1)  You can't divide by 0. 

    2)  You should easily be able to filter out any values you do NOT want to display.

    3)  If you want to do any complex calculations, you should use the getpivotdata function, or copy/pastespecial/values to another sheet and do your calculations in a helper column, with references to the copied pivot items.

    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Thursday, March 19, 2015 2:04 PM