Pivot Table Value Filters - expand criteria for calculated fields.

[adops]

Pivot Table Help: Microsoft Office 2007 -

Hi! I need to filter values from a calculated field. The calculated field is a %, and I need to filter so I only view values greater than or equal to a certain % (65% in this instance). I am viewing 6 months of data, and the filter is using the 6 months % average. By taking the average, I am not seeing some rows where the calculation is over 65% in a specific month, if the overall 6 month average is below 65%. An example is an entry had March-July as the value 0%, but in August the value is 80%. Since the filter applies to all March - August, the grand total for that row is below 65%, so I don't see the issue in August.

Is there any way I can set a calculation to show me which rows have data greater than or equal to 65% in ANY month and not just the row's grand total average? Thanks!

 

[Fazza]

hi.

It sounds like you could filter the dataset via SQL to only have the data you want to see. At the same time you could probably replace the calculated field within the SQL. This might be not simple; hard to say without knowing much about the specific data set up.

Alternatively, have two pivot tables. First pivot table can have the average value for all months. Then add another column (or more than 1 column if you wanted) to the source data with a formula to ultimately return a TRUE/FALSE corresponding to whether or not that record will be in the final report - by reading data from the first pivot table. Such as using GETPIVOTDATA formula or VLOOKUP or whatever. The second pivot table can use this new field as a page field to selectively filter the data as desired. (BTW, instead of the first pivot table, you might instead have a query table.) You will need I think to refresh the first pivot (or query) table and then the second table to get the correct result.

hth

 

[adops]

Thank you so much for your help, Fazza! I did try what you recommended with the two pivot tables (I'm still very much a novice with queries), with the extra column acting as the true/false filter.

What I ultimately need to do is see any of the rows in which at least one of the months contains a value above 65%. I was thinking - for simplicity sake to keep it down to one pivot table, could I find the max of the Grand Total column if it's a calculated field? I've tried to change the Value Field Setting but the % stays the same regardless if it's a sum/max/min/average of the values.

 

[Fazza]

I don't understand the details, sorry, so can't advise. Repeating my original answer is the best I could advise, I suspect. I assume you had success with the two pivot table approach.

BTW, I almost never user caclulated fields, so not able to give much advice on that sorry; I use SQL instead - it is extremely powerful. Another thought, maybe create what you want in MS Access & then copy the SQL to do the same thing in Excel.