Finding Conversion ratio using Pivot Table Calculated Items

Today, lets understand how to use Calculated items feature in Pivot tables. We will use a practical problem many of us face to learn this feature – ie calculating conversion ratio from a list of sales calls.

This is inspired from a question posted by Nicki in our forums,

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?

A look at the data

Lets say, you have some data like this and you want to understand what is the conversion ratio by location.

Setup a pivot table

The first step is to just create a pivot table from this data. Put locations in row labels area, status in column labels are and ID in values area. Now you will have a count of items for each status in each location. Something like this:

Add a calculated item to get conversion ratio

Now we want to calculate how much percentage is “booked” status items in all items for a location. To do this,

1. Select any column label item in the pivot table.
2. Click on Pivot Options > Fields, Items & Sets > Calculated item
   
3. Give your calculated item a suitable name like Conv. %
4. Write the formula = Booked / (Booked + Pending + Lost)
   
5. Click ok.

Now you should see another column in your pivot table with calculated item – Conversion %.

Formatting Conversion % in Percentage format

While we got what we wanted, it is not looking alright. We need to format the conversion % so that it looks alright. For this,

1. Right click on any value in pivot table
2. Go to value field settings
3. Click on number format
4. Select custom
5. Type the custom formatting rule [>=1]0;[<1]0%;””
6. This will automatically transform all numbers smaller than 1 (ie all conversion %s) to percentage format while keeping everything else normal.
7. Done!

Resource: Learn more about custom number formatting

A video tutorial explaining this & more

Since calculated items can be somewhat tricky, I made a short video explaining how this works. In the video you can also see how to use Power Pivot measures to calculate conversion ratios easily. Watch it below (or on our youtube channel).

Download Example workbook

Click here to download example workbook. It has both regular and powerpivot based calculations. Go ahead and examine them. Enjoy.

Do you use Calculated items?

I find calculated items to be very tricky to work with. In most cases, I try to add extra calculations to original data table or use formulas instead. But this example is a good case where calculated item is perfect.

What about you? Do you use Calculated items? In what situations you use them? Please share your experiences and tips using comments.

Convert your self to a Pivot table pro…

If you are use Excel pivot tables & data analysis features, then you will find below resources very useful.

• Pivot table tips & tricks
• Grouping data in Pivot tables
• 6 part tutorial: Using pivot tables for financial reporting
• Using report filters in pivot tables
• Using slicers in Excel 2010 pivot tables to create powerful dashboards
• Introduction to Power Pivot