Have pivot show only data with certain measure values?

[greend21]

I have a pivot table with 3 dimensions that has SUM(NetAmt) as the measure. I only want to show records on the pivot when the measure is between -50 and 50. Is there any way to do this? I tried an if statement to ignore the other values but then to remove them I need to deselect "Include Zero Values." I need to include zero values because I need to see where the sum offsets to 0 as well. I even added an IF statement setting $0.00 as TEXT($0.00) but then it puts the text values off to the left while the number values are off to the right. Does anyone know of another way?

[Rodj · Accepted solution]

You can probably do this with a calculated dimension. By way of example, I have a dummy data set with Dept and Year as dimensions Rev as a measure. if I put them in a pivot table I can change the dimension expression to be:

=if(Aggr(Sum(Rev), Dept, Year) < 50, Dept) //for Dept

and uncheck "Include null values" in the dimension properties. This then works to hide  values less than 50.

Cheers,

Rod

[greend21]

This worked great. Thanks!