Filtering particular values in a dimension - pivot Table

[Anonymous]

Hi all,

I am having a criteria to filter the zone 1 only in my pivot table from zone dimension.I attached the excel sheet data and expected output for the same.Can anyone suggest the way to complete it.

Input table for the pivot table:

Expected pivot table output:

[johnw]

So you want the pivot table to show only Zone 1? Perhaps use a calculated dimension and suppress when null?

if(Zone='Zone 1',Zone)

[blaise]

Or if your expressions allows move the if statement inside the expression rather then making a calculated expression (better performance as John earlier stated in another post).

F.ex. If your expression is sum(Revenue) make sum(if(Zone='Zone 1',Revenue,null()) and supress null on Zone dimension.

An ever better solution is to move the if statement to the script.

[johnw]

Good points. I think you're right that the best solution is to move it into the script. In the same table that has the Zone field, add a new field:

if(Zone='Zone 1',Zone) as "Zone 1",

Then use "Zone 1" as your dimension instead of Zone, and suppress null values.