Excel 2007 Pivot Table - Problem with custom field?

  • Thread starter Thread starter michael.beckinsale
  • Start date Start date
M

michael.beckinsale

Hi All,

I am trying to do what l think is pretty basic calculation & filter
exercise with a pivot table but l cant get it right.

The pivot table scenario is something like this:

Report Filter = Company
Row Labels = Reporting Officer, Reporting Category, Cost Centre
Column Labels = Sum Values
Sum Values = YTD Actuals, YTD Budget, YTD Variance (Non are
calculated, they all come from source data)

The above works fine as far as it goes but what l really want to do is
only show the records where the variance is + or - 100,000

I have tried adding calculated fields etc but just cant get the
desired result. Adding a column to the source data is not an option
because 'Account Code' is the next level down and sub totals would be
required in the source data. Additionally l would really like to avoid
a calculation outside the pivot table as a report has to be run for
several different company's.

Surely this can be achieved?

All help / ideas greatly appreciated

Regards

Michael
 
Hi,

Maybe, add the Variance field to the Row Label area (as well as the Value
area) and filter on it there.

If this helps, please click the Yes button

Cheers,
Shane Devenshire
 
Right-click on one of the Cost Centre items
Click on Filter, then click Value Filter
In the first drop down, select Sum of YTD Variance
In the second drop down, select Is Between
Enter the Min and Max values, then click OK
 
Back
Top