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
Variety	Dispatched Quantity Total TCE	% of Estimate
English Cox	Apples	SALES ESTIMATE	English Cox
Orin	Apples	SALES ESTIMATE	Orin
English Cox	Apples	SALES ESTIMATE	Golden
Orin
English Cox
Orin	Variety	Orin
English Cox
Orin	Row Labels	Sum of Dispatched Quantity Total TCE	Sum of % of Estimate
Orin	8
English Cox	9
English Cox	10
Orin	11
Orin	12
Orin	Grand Total

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]600[/TD]
[TD="align: right"]0.001[/TD]
[TD="align: right"][/TD]

[TD="align: right"]600,000.00[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]1,800.00[/TD]
[TD="align: right"]0.006[/TD]
[TD="align: right"][/TD]

[TD="align: right"]300,000.00[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]6,000.00[/TD]
[TD="align: right"]0.01[/TD]
[TD="align: right"][/TD]

[TD="align: right"]300,000.00[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]1,000.00[/TD]
[TD="align: right"]0.003333333[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]1,000.00[/TD]
[TD="align: right"]0.001666667[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]1,000.00[/TD]
[TD="align: right"]0.003333333[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]8,070.00[/TD]
[TD="align: right"]0.01345[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]1,000.00[/TD]
[TD="align: right"]0.003333333[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]8,070.00[/TD]
[TD="align: right"]0.0269[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1800[/TD]
[TD="align: right"]0.60%[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]1,000.00[/TD]
[TD="align: right"]0.001666667[/TD]
[TD="align: right"][/TD]

[TD="align: right"]3000[/TD]
[TD="align: right"]1.00%[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]2,239.94[/TD]
[TD="align: right"]0.003733233[/TD]
[TD="align: right"][/TD]

[TD="align: right"]16979.82[/TD]
[TD="align: right"]5.66%[/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]2,264.15[/TD]
[TD="align: right"]0.007547167[/TD]
[TD="align: right"][/TD]

[TD="align: right"]19507.7[/TD]
[TD="align: right"]6.50%[/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]2,285.67[/TD]
[TD="align: right"]0.0076189[/TD]
[TD="align: right"][/TD]

[TD="align: right"]24220[/TD]
[TD="align: right"]8.07%[/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]1,000.00[/TD]
[TD="align: right"]0.003333333[/TD]
[TD="align: right"][/TD]

[TD="align: right"]65507.52[/TD]
[TD="align: right"]0.2183584[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=D2/VLOOKUP(C2,$I$2:$J$4,2,0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



(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