Pivot table - show as % of another sub-total

[meileetan]

My pivot table displays employee names in rows. I added in Capacity and Demand columns, and also their sub-status of Operational, Confirmed or Provisional. The Capacity column will only have "Confirmed" status. Is there a way to display the values as a % of the Capacity sub-total, i.e. over 14 in this example?

 

[ExceLoki]

you can do a value field setting for each. in the example above i was doing it to the demand total column.

 

[meileetan]

View attachment 54510
you can do a value field setting for each. in the example above i was doing it to the demand total column.

"Operational", "Confirmed", "Provisional" are not base fields but the values of the field "Status".
How did you get "14" to appear in the base item?

I'm attaching a sample of the source data, if this helps.

Book1
	A	B	C	D
1	Person	Demand/Capacity	Status	Days
2	A	Capacity	Confirmed	14
3	B	Capacity	Confirmed	14
4	C	Capacity	Confirmed	14
5	D	Capacity	Confirmed	14
6	E	Capacity	Confirmed	14
7	F	Capacity	Confirmed	14
8	G	Capacity	Confirmed	14
9	H	Capacity	Confirmed	14
10	I	Capacity	Confirmed	14
11	A	Demand	Confirmed	0.55
12	B	Demand	Confirmed	5.4
13	C	Demand	Confirmed	16.2
14	D	Demand	Confirmed	3.8
15	E	Demand	Confirmed	13.5
16	F	Demand	Confirmed	18.8
17	G	Demand	Confirmed	10.2
18	H	Demand	Confirmed	0
19	I	Demand	Confirmed	3.5
20	A	Demand	Provisional	8.2
21	B	Demand	Provisional	0
22	C	Demand	Provisional	0
23	D	Demand	Provisional	0
24	E	Demand	Provisional	0
25	F	Demand	Provisional	0
26	G	Demand	Provisional	0
27	H	Demand	Provisional	4.3
28	I	Demand	Provisional	0
Sheet1

 

[ExceLoki]

so i had to create a calculated field that was days/14, i named it "percent of cap." then changed it to show percentage values. then i hid the column for the capacity total calculated field.
---------------

pivot percent of sub total.xlsx
	H	I		K	L	M	N	O	P
1		Column Labels
2		Capacity		Demand				Demand Capacity Total	Demand Percent Of Cap
3				Confirmed		Provisional
4	Row Labels	Capacity Total		Capacity Total	Percent Of Cap	Capacity Total	Percent Of Cap
5	A	14		0.55	3.93%	8.2	58.57%	8.75	62.50%
6	B	14		5.4	38.57%	0	0.00%	5.4	38.57%
7	C	14		16.2	115.71%	0	0.00%	16.2	115.71%
8	D	14		3.8	27.14%	0	0.00%	3.8	27.14%
9	E	14		13.5	96.43%	0	0.00%	13.5	96.43%
10	F	14		18.8	134.29%	0	0.00%	18.8	134.29%
11	G	14		10.2	72.86%	0	0.00%	10.2	72.86%
12	H	14		0	0.00%	4.3	30.71%	4.3	30.71%
13	I	14		3.5	25.00%	0	0.00%	3.5	25.00%
14	Grand Total	126		71.95	513.93%	12.5	89.29%	84.45	603.21%
Sheet1

 

[meileetan]

That's a great workaround, thank you for that. Unfortunately, I would need it to be dynamic as the Capacity figure will change when other filters are applied.

 

[ExceLoki]

the problem i was running into is that with the given data you're asking to divide days by days, so it doesnt know which day value to divide by the other. i came up with a solution if you are able to add another column to your table. take a look below and let me know what you think. i added a vlookup to return the first days value based on person. so this should work as long as capacity is the first set of days. then i added a calculated field that was days/compare, i named it "percent of cap." then changed it to show percentage values. then i hid the column for the capacity total calculated field. let me know if you have any questions.
-------------------

pivot percent of sub total.xlsx
	A	B	C	D	E	F	G	H	I				M	N	O	P	Q	R	S	T
1	Person	Demand/Capacity	Status	Days	compare				Column Labels
2	A	Capacity	Confirmed	14	14				Capacity				Demand						Demand Capacity Total	Demand Sum of Percent of Cap
3	B	Capacity	Confirmed	14	14				Confirmed				Confirmed		Provisional		Operational
4	C	Capacity	Confirmed	14	14			Row Labels	Capacity Total				Capacity Total	Sum of Percent of Cap	Capacity Total	Sum of Percent of Cap	Capacity Total	Sum of Percent of Cap
5	D	Capacity	Confirmed	15	15			A	14				0.55	3.93%	8.2	58.57%	0	0.00%	8.75	20.83%
6	E	Capacity	Confirmed	15	15			B	14				5.4	38.57%	0	0.00%	1.55	11.07%	6.95	16.55%
7	F	Capacity	Confirmed	15	15			C	14				16.2	115.71%	0	0.00%	0	0.00%	16.2	38.57%
8	G	Capacity	Confirmed	16	16			D	15				3.8	25.33%	0	0.00%	0	0.00%	3.8	8.44%
9	H	Capacity	Confirmed	16	16			E	15				13.5	90.00%	0	0.00%	0	0.00%	13.5	30.00%
10	I	Capacity	Confirmed	16	16			F	15				18.8	125.33%	0	0.00%	0	0.00%	18.8	41.78%
11	A	Demand	Confirmed	0.55	14			G	16				10.2	63.75%	0	0.00%	0	0.00%	10.2	21.25%
12	B	Demand	Confirmed	5.4	14			H	16				0	0.00%	4.3	26.88%	0	0.00%	4.3	8.96%
13	C	Demand	Confirmed	16.2	14			I	16				3.5	21.88%	0	0.00%	3.5	21.88%	7	14.58%
14	D	Demand	Confirmed	3.8	15			Grand Total	135				71.95	53.30%	12.5	9.26%	5.05	3.74%	89.5	22.10%
15	E	Demand	Confirmed	13.5	15
16	F	Demand	Confirmed	18.8	15
17	G	Demand	Confirmed	10.2	16
18	H	Demand	Confirmed	0	16
19	I	Demand	Confirmed	3.5	16
20	A	Demand	Provisional	8.2	14
21	B	Demand	Provisional	0	14
22	C	Demand	Provisional	0	14
23	D	Demand	Provisional	0	15
24	E	Demand	Provisional	0	15
25	F	Demand	Provisional	0	15
26	G	Demand	Provisional	0	16
27	H	Demand	Provisional	4.3	16
28	I	Demand	Provisional	0	16
29	A	Demand	Operational	0	14
30	B	Demand	Operational	1.55	14
31	C	Demand	Operational	0	14
32	D	Demand	Operational	0	15
33	E	Demand	Operational	0	15
34	F	Demand	Operational	0	15
35	G	Demand	Operational	0	16
36	H	Demand	Operational	0	16
37	I	Demand	Operational	3.5	16
Sheet1 (3)

Cell Formulas
Range	Formula
E2:E37	E2	=VLOOKUP([@Person],A:D,4,FALSE)

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