Add percentage column in Pivot Table

[excelpunk]

Hello All, I am not able to add a percentage column in a pivot table.

My data looks like this:

Name Location Call Date Subscription Date State Country
John Hackensack 4/4/2017 4/29/2017 NJ USA
Peter Fort Colins 4/7/2017 CO USA

In my pivot table, I have added count of call date and count of subscription date as my columns along with Location, State and Country as row labels.

I have also created a calculation field (Conversion Ratio) and defined it as 'Subscription Date'/'Call Date'. but I dont see accurate conversion %. For NJ, it calculates conversion ratio as 100.1% but it has to be exactly 100%. I am not sure what I am doing wrong. Can someone please guide.

Thanks!

 

[RoryA]

You can't use a count in a calculated field - they always sum. Since dates are treated as numbers, you are actually dividing 4/29/2017 by 4/4/2017, which is equivalent to dividing 42854 by 42829.

If you don't have power Pivot available, you can add two new counter fields to the source data that return 1 for each call date and 1 for each subscription date (0 if the date is missing) then sum those fields to get a count. You can also then divide one field by the other to get a percentage.

 

[excelpunk]

Oh! Finally. Thanks for the work around. It worked like charm.

 

[RoryA]

Glad to help. Not sure that a 22 minute turn around merits "finally" though.

 

[excelpunk]

No it was not about the time it took someone to reply here on the forum. I have been searching for it online as I felt this is quite simple but I may be doing something wrong. I did not find a solution so I posted it. Thanks Again!