Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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
This worked great. Thanks!