Confused on Pivots - how to only include original data >= x

[JohnKauffman]

I have never before had to perform this simple objective with PT.

How can I make a rule that determines which values from the original data are used in the aggregate? For example if MyCol is the aggregate, how can I only include values from MyCol that are >10? Note that MyCol is used as the aggregate, not a row or col. Row and Col fields can be filtered with Value Filter, Label Filter, Checkboxes

Example:
Worksheet of house sales. Each row is one sale. Cols are ID, Price, Type, City.
PT has Type as rows, City as col and Price as the aggregate.
How can I include in the PT aggregate results only the cases where the price was <$200?

 

[Jerry Sullivan]

The PivotTable itself can only filter using the aggregate values. Two options to filter based on the individual records are:

1. Add an additional field to the data source. Use a formula in that field that will evaluate to True or False based on whether each record meets the criteria.
This added field can then be used as a Report Filter to restrict the data reported to those records meeting the criteria.
The formula can reference a cell on the same sheet as the Pivot to allow dynamic changes to the criteria, however the PivotTable will need to be refreshed any time the criteria is changed.

2. An SQL query can be used to return a modified dataset which is then used as the datasource for the Pivot. The query could add a field that would allow Filtering by record (similar to Option #1), or it could restrict the records placed in the datasource to those meeting the criteria.

 

[JohnKauffman]

Thanks, Jerry. Now I understand problem & can use your solution.