+ Reply to Thread
Results 1 to 1 of 1

Pivot - adding a calculated field based on grouped totals, NOT source fields

  1. #1
    Registered User
    Join Date
    Marin County, CA
    MS-Off Ver
    Excel 2007

    Pivot - adding a calculated field based on grouped totals, NOT source fields

    Think of a pivot table whose source data is something like the following:

    (each row represents a sales opportunity)

    Opportunity Name | Amount | Source

    Opp 1 | $100 | Alpha
    Opp 2 | $200 | Alpha
    Opp 3 | $50 | Beta
    Opp 4 | $100 | Beta

    I've grouped the opportunites together by Source, which is one of the Column Labels of my pivot table - so, for example, I will group opps by Fiscal Quarter, then subgroup them by Alpha and Beta, so for each quarter I have totals for Alpha opps and Beta opps.

    The calculated field I want to add would show the difference between the TOTALS of the Alpha and Beta groups for each quarter. Something like the following, where I would have an additional column which would = (Alpha-Beta) for each total:

    Alpha--|-- Beta--|--Variance--||--Alpha--|--Beta--|--Variance--||
    $150 --|--$200--|-- ($50)-----||--$300--|--$100--|--$200-----||

    Unfortunately, from what I can see Calculated Fields only work with the original source table's columns. I can't use it to write formulas using grouped totals as variables. Can anyone tell me if this is possible?
    Last edited by smohyee; 11-01-2013 at 11:49 AM.

+ 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. Replies: 0
    Last Post: 06-26-2012, 09:06 PM
  2. Replies: 0
    Last Post: 07-10-2010, 03:02 PM
  3. Replies: 1
    Last Post: 10-02-2006, 09:56 AM
  4. [SOLVED] Calculated Field in Pivot Table Based on Two Counted Fields
    By cmlits in forum Excel General
    Replies: 1
    Last Post: 03-30-2006, 12:50 AM
  5. Totals of calculated field in pivot table give incorrect results
    By Jake in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-12-2006, 02:20 PM

Tags for this Thread


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