Calculated fields in pivot tables

[nicki.french]

Hi there

This is my first post a I really hope you can help as its driving me bananas.


I have a spreadsheet source data full of sales enquiries which have the Status - Lost, Booked or Pending. Each sales enquiry relates to a particular location. I have created a pivot table which counts the enquiries and displays them with the Locations in rows and the Status in the columns. I have got a row total showing the total number of sales enquiries for each location. I also want my table display the sales conversion number, ie the booked enquiries as a % of the total enquiries. How do I do this?

 

[kaushik03]

Hi Nicki,


Welcome to the forum and we glad to have u here.


Could you plz upload your sample data here?

http://chandoo.org/forums/topic/posting-a-sample-workbook


Regards,

Kaushik

 

[srinidhi]

Insert the required formula in the value field settings in the Options tab

 

[nicki.french]

I can't upload because my computer is locked down and I can't download the software fr drop box etc. Here is a simple view of the table and the data that created it.


Count of Removal

Booked Lost Pending Grand Total want to display booked as a percentage of the total in this column

Adelaide 1 1 1 3

Sydney 1 2 1 4

Grand Total 2 3 2 7


Removal Client Branch Cost Sale Price Margin Margin% Status

1 Smith Sydney 1000 2000 1000 100 Booked

2 Jones Adelaide 500 750 250 50 Lost

3 Wenlock Sydney 750 1000 250 33 Pending

4 Peace Sydney 250 500 250 100 Lost

5 Which Sydney 300 400 100 33 Lost

6 Maker Adelaide 1500 2000 500 33 Pending

7 Breel Adelaide 100 150 50 50 Booked


Thanks for looking both of you. Srinidhi, I know where to put the formula, the point is that I can't insert one that works. I just get error messages that it won't accept the formula. I have ungrouped the items so I can do a calculated item but still cant get one that works.

 

[nicki.french]

I can't upload because my computer is locked down and I can't download the software fr drop box etc. Here is a simple view of the table and the data that created it.


Count of Removal

Booked Lost Pending Grand Total want to display booked as a percentage of the total in this column

Adelaide 1 1 1 3

Sydney 1 2 1 4

Grand Total 2 3 2 7


Removal Client Branch Cost Sale Price Margin Margin% Status

1 Smith Sydney 1000 2000 1000 100 Booked

2 Jones Adelaide 500 750 250 50 Lost

3 Wenlock Sydney 750 1000 250 33 Pending

4 Peace Sydney 250 500 250 100 Lost

5 Which Sydney 300 400 100 33 Lost

6 Maker Adelaide 1500 2000 500 33 Pending

7 Breel Adelaide 100 150 50 50 Booked


Thanks for looking both of you. Srinidhi, I know where to put the formula, the point is that I can't insert one that works. I just get error messages that it won't accept the formula. I have ungrouped the items so I can do a calculated item but still cant get one that works.