Finding Fraction of 2 calculated fields in Pivot Table

[jenna234]

[FONT=&quot]I have a table which summaries a list of purchases made by customers. In this table it has 3 columns, the customer ID, their max budget and the purchase amount. I wish to make a pivot table which shows compiles the sum of purchases for each customer and the % of their budget used. I had no problem making the sum of purchases amount but am having difficulty using a pivot table to make the % used field. [/FONT]
[FONT=&quot]I've tried to make a calculated field using: = SUM( 'Purchase Amount ($)')/ 'Max Budget ($)'[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]But it does not take into account the sum of the purchase amount. It just uses 1 of the values from one purchase rather than a sum of all the purchases. [/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]C1 should have 22% used (with 2 purchases) and C2 with 25% used (with 3 purchases) but instead it returns C1 with 11% and C2 with 8.33%.[/FONT]

 

[FDibbins]

x-posted here
https://www.excelforum.com/excel-ch...on-of-2-calculated-fields-in-pivot-table.html

 

[sheetspread]

Add a quotient column to the source data. I don't think calculated fields can selectively aggregate:

Excel 2010
	A	B	C	D	E	F	G	H
1	Customer	Max Budget	Purchase Amount	%	Row Labels	Max of Max Budget	Sum of Purchase Amount	Sum of %
2	C1	50	5	0.1	C1	50	11	0.22
3	C1	50	6	0.12	C2	100	25	0.25
4	C2	100	7	0.07
5	C2	100	8	0.08
6	C2	100	10	0.1
Sheet14

Cell Formulas
Range	Formula
D2	=C2/B2

 

[RoryA]

Welcome to the forum.

Do you have Power Pivot available?