Pivot table - Relative values to one value

[#1]

Hi,

I have a pivot table with one dimension as rows (level), and another as columns (location).

I have a master location (eg London), and I want to show the median totals for this location.

I want all of the other locations to be calculated based on their relativity to London.

So row one would look like this

Level     Loc1     Loc2     Loc3     Loc4     London

a               -15%     20%     25%     -8%     100,000

Is this possible?

I have tried saving the London value as a variable, and this doens't work.

Would it be easier to create 5 columns, each individually calculated as the median total for each location, rather than using a pivot table?

Thanks,

Joe

[a_mullick]

Hi,

Please find attached an example of what I think you're looking for. The selection of the master city is a bit naive, but hopefully you can adapt it to suit your model. 'Last' is a chart inter record function, so your master city would always need to be last in sequence for this to work.

Hope it helps

Azam

[a_mullick]

...and here's a version using a variable as you suggested in your message. the behaviour is perhaps more 'logical' if you select a location.

Azam