Pivot Table -> Show Value As -> % Of (Cumulative)

[AnAppleADay]

Trying to find a workaround to show a cumulative '% percent of' (currently using 'show values as'), so running up to 100% by the end of the year, hopefully more if we exceed sales expectations.

I have uploaded an example of the information i am working with as it may help to see the data - I listed 'SALES ESTIMATE' as a week number in my pivot's data source, in order to use the estimate in my 'percent of' column, as i couldn't find another way. I'm not sure if i need to just amend a setting slightly or fully change how i am referring to my estimated sales figure.

Any help on this would be very much appreciated, i am quite new to PTs.

Thank you!

https://onedrive.live.com/embed?cid...D1DAF5E07887!205&authkey=ACqXc1plaZnW72Y&em=2

 

[AnAppleADay]

Sorry the first paragraph should have read:

Trying to find a workaround to show a cumulative '% percent of' (currently using 'show values as') - based on an estimated number of sales for the year - so running up to 100% by the end of the year, hopefully more if we exceed sales expectations.

 

[sheetspread]

I listed 'SALES ESTIMATE' as a week number in my pivot's data source, in order to use the estimate in my 'percent of' column, as i couldn't find another way. I'm not sure if i need to just amend a setting slightly or fully change how i am referring to my estimated sales figure.

Instead, consider a separate table for the estimates then write a vlookup formula as the denominator:

Excel 2010
	C	D	E	F	G	H	I	J
1	Variety	Dispatched Quantity Total TCE	% of Estimate
2	English Cox	600	0.001		Apples	SALES ESTIMATE	English Cox	600,000.00
3	Orin	1,800.00	0.006		Apples	SALES ESTIMATE	Orin	300,000.00
4	English Cox	6,000.00	0.01		Apples	SALES ESTIMATE	Golden	300,000.00
5	Orin	1,000.00	0.003333333
6	English Cox	1,000.00	0.001666667
7	Orin	1,000.00	0.003333333		Variety	Orin
8	English Cox	8,070.00	0.01345
9	Orin	1,000.00	0.003333333		Row Labels	Sum of Dispatched Quantity Total TCE	Sum of % of Estimate
10	Orin	8,070.00	0.0269		8	1800	0.60%
11	English Cox	1,000.00	0.001666667		9	3000	1.00%
12	English Cox	2,239.94	0.003733233		10	16979.82	5.66%
13	Orin	2,264.15	0.007547167		11	19507.7	6.50%
14	Orin	2,285.67	0.0076189		12	24220	8.07%
15	Orin	1,000.00	0.003333333		Grand Total	65507.52	0.2183584
Sheet1

Cell Formulas
Range	Formula
E2	=D2/VLOOKUP(C2,$I$2:$J$4,2,0)

(Power pivot is even better for this, especially with many records)

 

[AnAppleADay]

Thanks, good thought - i will go and have a play with Vlookup!

 

[AnAppleADay]

Instead, consider a separate table for the estimates then write a vlookup formula as the denominator:

Excel 2010

	C	D	E	F	G	H	I	J
1	Variety	Dispatched Quantity Total TCE	% of Estimate
2	English Cox	600	0.001		Apples	SALES ESTIMATE	English Cox	600,000.00
3	Orin	1,800.00	0.006		Apples	SALES ESTIMATE	Orin	300,000.00
4	English Cox	6,000.00	0.01		Apples	SALES ESTIMATE	Golden	300,000.00
5	Orin	1,000.00	0.003333333
6	English Cox	1,000.00	0.001666667
7	Orin	1,000.00	0.003333333		Variety	Orin
8	English Cox	8,070.00	0.01345
9	Orin	1,000.00	0.003333333		Row Labels	Sum of Dispatched Quantity Total TCE	Sum of % of Estimate
10	Orin	8,070.00	0.0269		8	1800	0.60%
11	English Cox	1,000.00	0.001666667		9	3000	1.00%
12	English Cox	2,239.94	0.003733233		10	16979.82	5.66%
13	Orin	2,264.15	0.007547167		11	19507.7	6.50%
14	Orin	2,285.67	0.0076189		12	24220	8.07%
15	Orin	1,000.00	0.003333333		Grand Total	65507.52	0.2183584

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula E2 =D2/VLOOKUP(C2,$I$2:$J$4,2,0) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

(Power pivot is even better for this, especially with many records)

Power Pivot looks smart - I'd like a go on that - sadly, my mac does not seem to 'do' Add-ins. Clearly Apple are to up themselves to even consider needing an *eye roll* .. add-in! :P

So the table seem to be a lot less junkie than before - However, i still can't find out to make the % column (Sum of % Estimate) into a % cumulative - Is that possible with the data presented how it is now?

Thank you again!

 

[sheetspread]

Show values as running total in Week Number base field:

Excel 2010
	G	H	I
7	Variety	Orin
8
9	Row Labels	Sum of Dispatched Quantity Total TCE	Sum of % of Estimate
10	8	1800	0.60%
11	9	3000	1.60%
12	10	16979.82	7.26%
13	11	19507.7	13.76%
14	12	24220	21.84%
15	Grand Total	65507.52
Sheet1