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:
Q1--------------------------------Q2
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?
Bookmarks