How to calculate conversion rates in a pivot table using grouped data (Calculated Item won't work)

Question

Hi

So, I am analysing the number of people who sign up to a trial, how many of those convert into members and of those, how many subsequently cancel.

I have the numbers, but I want to show the percentage between each column, not as an overall total. So if 10 signed up, 5 became members, I want to see the conversion of 50%. If 1 of those subsequently cancelled, I want to see 20. I can't use Calculated Item as it's grouped data by Year/Month (and needs to be grouped in that way) so how do I do this so that it automatically calculates and updates?

I've tried uploading a screenshot but it won't let me (keeps saying failed - tried png, jpg and file size is tiny so not sure why).

I'll paint a picture by words. 4 columns:

Column 1: Year/Month

Column 2: Number of trialist sign ups

Column 3: Of those, number of member sign ups

Column 4: Of member sign ups, number that cancel

So Column 3b would be a percentage of 2 and 3, and Column 4b would be a percentage of 3 and 4. Does that make sense? To be clear, I don't want the numbers to be a % of the total column/parent row or anything else. Just a % of the previous column.

My brain has fused and I can't get any further until I (or, rather, you) figure this out. Thank you for helping. :)

Warm regards

JayVee

Answer 1

Hi Ashish

Thanks for your reply. I've never heard of Power Pivot but I can see that it's an add-on and have just activated it, although I don't know how to use it. I've provided a link to the test file with the current pivot. There are two sheets - the main data, and the report tab.

https://www.dropbox.com/s/sy122jdo99bcc7l/Master%20MI.xlsx?dl=0 

I have the numbers, but I want to show the percentage between each column, not as an overall total. So if 10 signed up, 5 became members, I want to see the conversion of 50%. If 1 of those subsequently cancelled, I want to see 20.

By 20, I mean 20% in that example - see above.

Thanks for your help.

Jackie

Answer 2

Hi,

I'd rather solve it with the DAX language of the PowerPivot. Does your version of MS Excel support the PowerPivot? If yes, then share the download link of the file. Also, in your original question you mention that "If 1 of those subsequently cancelled, I want to see 20". What is 20?

Answer 3

Excel 365 Pro Plus with Power Pivot and Power Query.

Calculate %.

No Calculated Item/Field in PP, Calculated Column instead.

PQ used instead of CC or DAX.

With fancy PivotChart.

https://www.mediafire.com/file_premium/zw8miedej62pfer/04_16_23.xlsx/file

https://www.mediafire.com/file_premium/v8rti1ehsvn2vay/04_16_23.pdf/file

Answer 4

Excel 365 Pro Plus with Power Pivot and Power Query.

Calculate %.

No Calculated Item/Field in PP, Calculated Column instead.

PQ used instead of CC or DAX.

With fancy PivotChart.

https://www.mediafire.com/file_premium/zw8miedej62pfer/04_16_23.xlsx/file

https://www.mediafire.com/file_premium/v8rti1ehsvn2vay/04_16_23.pdf/file

Answer 5

Thought I'd try loading it separately and it's worked this time.