Custom calculation for a field in a PivotTable report

[leocoppens]

Hi,

I'm trying to do something that should be very easy/intuitive (from my point of view) but it's not working... I explain in simple terms:

a) Table

Country Region Sales Cost
UK UK1 890 89
UK UK2 890 89
USA USA1 250 25
USA USA2 250 25


b) Create a pivot table from it and display the following,

Row Labels: Country
Region
Values: Sales
Cost


Up to now very easy. Now I want to display the cost as a percentage of the sales instead of showing it as a value.

What I do is:

1. Select the field "Cost" in the values area and show the "Value Field Settings"
2. Select the "Show values as" tab
3. Select Show values as "% Of" (Displays a value as a percentage of the value of the Base item in the Base field)
4. Select Base field: "Sales"
5. Select Base item: "(previous)"


Then, as I understand from the MSExcel Help, this should show my custom calculation.

The problem is that IT DOESN'T!!

The result is always "#N/A"...

Even if i try to perform a more basic calculation (if possible) like Show values as "Difference From" it will display the same not applicable message.

Anybody has a clue of what I'm doing wrong? or better, what I should be doing to get the cost to be displayed as a percentage of the price?

Thanks a lot for your interest!!!

Regards,
Leo

 

[RoryA]

You need to add a Calculated Field to your pivot using =Cost/Sales
If you are using 2003, it's in the Formulas section of the Pivot Table toolbar.

 

[leocoppens]

Hi Rorya!

Thanks for that, it works!

I have XL2007 and it's in The pivotTable Tools menu under "Tools" > "Formulas".

For this example i used a basic Pivot Table to explain it, now I still can't solve the same issue in the actual PivotTable I'm working as it's been fed by SQL Server Analysis services.

In this scenario the "Formulas" option appears to be disabled. Instead, excel is offering me the "OLAP tools" menu but this is asking me to convert my PivotTable to formulas... this is not what I want as I loose formats and other standard PivotTable functionality.

Thanks a lot anyway, that is a good solution!

Leo