Pivot Table calulations based on results in the Pivot Table

[BaconMcSandwich]

Hi,
I'm trying to add some calcualtions to my Pivot Table to calculate the % of enquiries converted to deals. I can easily add this outside the pivot table but as the number of rows will change in the live data I'm looking it add it to the table so that I can add formatting and don't have random formula's lying around.

The only success I've had is adding a column that relates to the "Number" in the raw data but it alwas Sum's it regardless of what formula I add in.

Can anyone help?

 

[ExceLoki]

you should be able to insert a calculated field that figures the number of deals / number of enquires.

then value field settings- number format- percentage

this worked for me.
let me know if still having issues.

 

[BaconMcSandwich]

Hi,
Cheers for the response. It's got me close but not quite working. (And I really hope this explanation is going to makes sense)
When I try and insert the formula based on my data/Pivot table Excel gets grumpy asking me, am i sure I'm inserting my formula properly and a helpful box explaining how formulas work.

When I've calculated the value for "deals" its though the normal pivot table analysis and a count of records. When I go into "Insert Calculated Field" I have two fields, the Make and Number (Column headings from my source table).

Looking at the screen shot for your "insert calculation" you have 3 fields, Make, Deals and Enquiries. How have you got that "deals" field as a field you can use as a calculation?

(I think that's the step im missing as I suspect mine is going wrong as Excel is looking at "make" as a text field so can't do calculations on it, you have the totals a seperate field with numbers so Excel can do calculations on it)

 

[BaconMcSandwich]

I've Changed the Image slightly to show where I'm at and removed the tidying up of the column headings in the pivot table if that helps figure out where I'm going wrong.

 

[ExceLoki]

to make it work i added a helper row. (sorry thought it was in the screen shots i shared before). if you are able to add this, it'll make it much easier.

----------------

Book1
	A	B	C
1	make	enquiries	deals
2	make1	15	4
3	make2	20	2
4	make3	9	6
5	make1	15	4
6	make2	20	2
7	make3	9	6
8	make1	15	4
9	make3	9	6
10	make3	9	6
11	make3	9	6
12	make1	15	4
13	make3	9	6
Sheet1

Cell Formulas
Range	Formula
C2:C13	C2	=COUNTIF($A$2:$A$13,A2)

 

[BaconMcSandwich]

Hi, Sorry for not getting back to you. I'm afraid that won't work with my live data as my live data is generated using powerquery to merge multiple tables from multople sheets together.

Cheers though,

 

[RoryA]

You need to use the data model. You can't use a count field result in a calculated field in a regular pivot table.