locked
excel 2016 -- add calculation to pivot table dividing one column by another, or one VALUE by another RRS feed

  • Question

  • Pivot tables aren't my strength.  I'm trying to add a calculated column to a pivot table that divides the VALUES "Sum of isOpen" by "Count of APPT SLOT STATUS".  But I only see the field names in the Calculated Fields box, not the aggregate VALUES.

    I looked at the GETPIVOTDATA() function but it doesn't seem to fit this particular scenario.

    Thanks for any assistance,

    Christian Bahnsen

    Friday, July 17, 2020 5:39 PM

All replies

  • Excel 365 Pro Plus with PowerPivot and Power Query.
    The evolved version of PivotTable over the last decade.
    http://www.mediafire.com/file/48f4umw3idunxi4/07_17_20.xlsx/file
    http://www.mediafire.com/file/wagajahsy9vobfp/07_17_20.pdf/file

    Friday, July 17, 2020 11:06 PM
  • I'd still like to figure out the issue as described above, but I came up with (what I consider to be) a workaround.

    In the table the pivot is based on, I added a "denominator" column with a 1 for each row.  I already had a formula to calculate whether column F (isOpen) would be a 1 or zero.

    Then in my pivot I was able to use the new column in a calculation:

    The new calculated column show the percentages I was shooting for:

    Not exactly the way I wanted to do it, but it works.

    Monday, July 20, 2020 1:23 PM
  • Herbert,

    thanks for the reply but I wasn't able to open those files.  I work at a DoD site and our firewalls blocked them.

    Chris

    Monday, July 20, 2020 1:27 PM