Calculating ppts difference from % of column total data in a pivot table

Hi, Can you post an example file?

Hi,
I need you to be clear with your explanations so I can follow what you want. On the 'Pivot Table' worksheet you want to subtract Cell 'D7' from Cell 'C7' & have the results displayed as a %?

Can you explain what you need in a similar way? I have no idea what 'capacity share data ppts vLY' means.

Hi sorry,

So basically the data shows the number of passengers that flew from London to a particular destination this year and last year. What I want to show is the market share for each carrier on each route (e.g. Chicago (CHI)) which is calculated by the total number of passengers that travelled from London to Chicago with a particular carrier divided by the total number of passengers that travelled from London to Chicago with any carrier.

I have calculated this for both this year and last year which you can see in the pivot table tab using the %of column total functionality. What I wish to show however is the difference in market share between this year and last year.

For example if American flew 200 passengers this year and 300 last year, and United flew 300 this year and 200 last year (for simplicity assume American and United are the only carriers passengers used to go from London to Chicago). American's share this year would be 40% compared to 60% last year so the vLY column would show -20% as their share was 20% lower than last year. United's share would be 60% this year and 40% last year so their share was 20% higher than last year.

The problem is that a simple calculative field does not work as it would do 200-300 = -100 (for American) and 300-200 = 100 (for United) but setting it as % of column total clearly won't give me the -20% as desired as the column total is 0.

Thanks for your help, Toby

EDIT: Sorry to make it more in excel speak I want to take the difference of the two columns in the pivot table (for each POS) which are in the form of % of column total

Hi Toby,

Thanks that's much clearer. I've attached your file with 2 pivots I've created.

One gives the total Pax for each Origin to Destination combination for all airlines.

The other is the same but for each airline.

Going from your info, you want to divide/subtract one from the other to get your market share result.

Am I understanding you correctly? If so, can you advise how you're getting these figures with your current method?

Cheers Peter

Sorry, forgot to add the file.