+ Reply to Thread
Results 1 to 4 of 4

Pivot table calculation field

  1. #1
    Registered User
    Join Date
    05-06-2021
    Location
    Winnipeg
    MS-Off Ver
    2016
    Posts
    2

    Question Pivot table calculation field

    Hello -- I hope someone can help me with a Pivot Table Calculation Field problem I worked on it, for hours today.
    Table has 3 columns

    1. Dept# (text)
    2. Type of amounts (text) 5 Types = Budget, Grant, Infrastructure, Projects, Technology
    3. Amounts (currency)

    Pivot table

    - Rows = Dept#
    - Columns = Type
    - Values = Amounts

    There are 5 columns of amounts. I want to calculate (Budget + Grant) -(Infrastructure + Projects + Technology) Can this be done in the Calculation field?
    Attached is a worksheet illustrating this quandary.

    Thank you for thinking about this problem
    BTTFin
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,082

    Re: Pivot table calculation field

    Hello BTTFin and Welcome to Excel Forum.
    1. Select cell F14
    2. Select Fields, Items & Sets from the Analyze sub tab of PivotTable Tools tab
    3. Select Calculated Item
    4. Put the following in the Formula window: =Budget +Grant -(Infrastucture +Projects +Technology )
    5. Select OK
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    05-06-2021
    Location
    Winnipeg
    MS-Off Ver
    2016
    Posts
    2

    Re: Pivot table calculation field

    Thank you JeteMc It works. Once I employed Calculation items vs Calculated fields, I was able to calculate a variance. Nonetheless as you guessed this tiny worksheet was constructed for demonstration purposes. Indeed, there was an additional "layer" nested into the department. When this method was employed to the added column the whole sheet expanded and crashed excel.. I must find another method.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,082

    Re: Pivot table calculation field

    One option might be to use power query.
    The following could be used as the advanced editor code:
    Please Login or Register  to view this content.
    Let us know if you have any questions.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Calculation field in Pivot Table
    By Eric Tsang in forum Excel Charting & Pivots
    Replies: 9
    Last Post: 01-03-2019, 12:19 PM
  2. Pivot Table Calculation Field
    By casper3043 in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 08-05-2015, 11:29 AM
  3. field calculation in a 3 dimensional pivot table
    By Shimoe in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 01-16-2013, 10:02 AM
  4. Calculated pivot table field using pivot table data in calculation
    By BrittleStar in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 12-10-2012, 05:35 PM
  5. Dynamic Variable in a pivot table calculation field
    By jwongsf in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-22-2012, 11:48 AM
  6. Calculation field in Pivot Table
    By Unica in forum Excel General
    Replies: 0
    Last Post: 11-30-2010, 03:52 AM
  7. Pivot Table Calculation Field Incorrect
    By alski in forum Excel General
    Replies: 1
    Last Post: 07-09-2009, 10:10 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1