Pivot Table Filtering by Value

[Jeffrey Lebowski]

Hi Everyone,

I have attached a sample of what I would like to accomplish using a Pivot Table.

Say I have a group of individuals to whom I would like to distribute samples of my products. Say that I have a limit of 2 on how much of any product I should give any one individual. The table in the attached worksheet can represent the actual distribution of samples. I would like to use a Pivot Table to show me ALL of the people who were given more samples than the limit of 2.

I have played around with pivots but have not been able to accomplish this. Any help is appreciated!

Thank you.

 

[Luke M]

Hi Jeffrey,

Needed to create a calculated field, and then filter on that. See attached.

 

[Jeffrey Lebowski]

Luke,

Can you tell me how you created that calculated field? That is exactly what I want to do, just have it replicated on a larger scale. Did you create the field like this?::



I am wondering if there might be another way to create this calculated field without referencing each individual data field. The file I replicate will need to pull the max from 42 data columns...

 

[NARAYANK991]

Hi Jeff ,

It is easier to put in the calculated field in your raw data table.

See the attached file.

Narayan

 

[Luke M]

Narayan stated it best. If you do it my way, you have to list all the fields, as the PT doesnt' keep track of spatial relationships. If you build the helper field, you can more easily use the MAX function with a range.

 

[Jeffrey Lebowski]

Thanks Luke & Narayan!