Excel 2007 Pivot Table - Problem with custom field?

[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

 

[Shane Devenshire]

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

 

[Debra Dalgleish]

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