none
Why is "Calculate Field" Greyed Out When Editing Pivot Table in Excel 2010? RRS feed

  • Question

  • OK, I'm trying to do something quite simple here in Excel 2010.  (I bought a $50 Microsoft Press book to quide me through the learning process about pivot tables--and I am following its advice--but still encountering problems.) 

    I have a pivot table with numeric columns entitled "Work" and "Capacity".  I simply want to create a calculated field (Capacity - Work)---but when I do as instructed--click "Fields, Items, and Sets" (under Pivot Table Tools), the "Calculate Field" command is greyed out. (?????)

    My data source is an OLAP cube in the Project Server database.

    Does anyone know why "Calculate Field" would be greyed out?


    Dr. Edward M. Hanna, PMP President Data Express, Inc. Irvine, CA emhanna@sbcglobal.net
    Monday, November 15, 2010 5:30 PM

Answers

  • OK, I have confirmation---

               You cannot create a calculated field or a calculated item in a PivotTable based on OLAP source data.

    Here is the KB article on the support website: http://support.microsoft.com/kb/234700 which describes the diffence in behavior of Pivot Tables depending on where your data comes from (i.e. OLAP data versus non-OLAP data).

    So--take heed--as this is not well documented.  My $50 MS Press book makes absolutely no mention of it.


    Dr. Edward M. Hanna, PMP President Data Express, Inc. Irvine, CA emhanna@sbcglobal.net
    • Marked as answer by Edward Hanna Monday, November 15, 2010 10:33 PM
    Monday, November 15, 2010 10:33 PM

All replies

  • I think I may have figured-out the problem.

    If I drive my Pivot Table from data that is included in the Excel 2010 spreadsheet, the "Calculate Field" button is available.  However, if the data is coming from an external source (i.e. my OLAP cube), it appears that the "Calculate Field" feature is not available.

    So--I'm thinking that I may need to go back and extend the OLAP cube by adding my calculated field there.  And then the field will become available for use in my Exel 2010 pivot table.

    I can--sort of--see the sense in this restriction--if I am correct about this.  It keeps the underlying data schema--in the OLAP cube--in sync with the data schema in-use in the Excel 2010 pivot table.

    The MS Press book says: "Custom fields and items can apply arithmetic operations to any data already in your Pivot-Table (including data generated by other custom fields or items), but they cannot reference worksheet data outside the Pivot Table".  Technically, my custom field would be referencing data that is in the Pivot Table, so--I thought--I should have been able to create a calculated field.

    I'm not totally satisfied that I understand what is going on here.  Still looking for better answers.


    Dr. Edward M. Hanna, PMP President Data Express, Inc. Irvine, CA emhanna@sbcglobal.net
    Monday, November 15, 2010 8:30 PM
  • OK, I have confirmation---

               You cannot create a calculated field or a calculated item in a PivotTable based on OLAP source data.

    Here is the KB article on the support website: http://support.microsoft.com/kb/234700 which describes the diffence in behavior of Pivot Tables depending on where your data comes from (i.e. OLAP data versus non-OLAP data).

    So--take heed--as this is not well documented.  My $50 MS Press book makes absolutely no mention of it.


    Dr. Edward M. Hanna, PMP President Data Express, Inc. Irvine, CA emhanna@sbcglobal.net
    • Marked as answer by Edward Hanna Monday, November 15, 2010 10:33 PM
    Monday, November 15, 2010 10:33 PM
  • Wednesday, August 22, 2012 12:03 PM
  • Add an existing Item to your pivot table, then place your cursor on that item.

    Calculated Item should no longer be grayed out.

     
    Wednesday, October 21, 2015 3:27 PM
  • It is grayed out because the source is OLAP, however there is a work around.

    Drop the data into Excel into a table.  If you try to pivot off this data, the calculated field will still be grayed out. BUT, if you make a dynamic range on the table and create a new pivot table that references the dynamic range of the table instead of the table itself, the calculated field will not be grayed out.

    How to do dynamic named ranges.

    Wednesday, November 16, 2016 9:11 PM
  • I found another variation (I used Excel 2013 for my testing).

    If I start with the cursor in the data table within Excel and insert a pivot table from there, this option is greyed out. I rarely use this approach so was surprised by the greyed out choice.

    If I start from a blank sheet, choose Insert, then Pivot table and choose the Excel data table range as my source, then it allows me to create calculated fields. I didn't have to create a dynamic range name, but it acted like one.

    Tuesday, January 24, 2017 6:33 PM
  • Found the answer when you first create the Pivot table and it is asking you where to put it, notice a box at the bottom called Add this data to data model.    If it is checked then seem Group by and Calculated fields go away.   I unchecked it and all started working again.
    Wednesday, October 4, 2017 12:21 AM
  • This works and it's easy. Thank you!!!
    Monday, September 10, 2018 3:58 PM