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

##### New Member
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=4918D1DAF5E07887&resid=4918D1DAF5E07887!205&authkey=ACqXc1plaZnW72Y&em=2

##### New Member
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.

##### Well-known Member
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:

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

Last edited:

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

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

Excel 2010
CDEFGHIJ
1VarietyDispatched Quantity Total TCE% of Estimate
2English Cox6000.001ApplesSALES ESTIMATEEnglish Cox600,000.00
3Orin1,800.000.006ApplesSALES ESTIMATEOrin300,000.00
4English Cox6,000.000.01ApplesSALES ESTIMATEGolden 300,000.00
5Orin1,000.000.003333333
6English Cox1,000.000.001666667
7Orin1,000.000.003333333VarietyOrin
8English Cox8,070.000.01345
9Orin1,000.000.003333333Row LabelsSum of Dispatched Quantity Total TCESum of % of Estimate
10Orin8,070.000.0269818000.60%
11English Cox1,000.000.001666667930001.00%
12English Cox2,239.940.0037332331016979.825.66%
13Orin2,264.150.0075471671119507.76.50%
14Orin2,285.670.007618912242208.07%
15Orin1,000.000.003333333Grand Total65507.520.2183584

</tbody>
Sheet1

Worksheet Formulas
CellFormula
E2=D2/VLOOKUP(C2,\$I\$2:\$J\$4,2,0)

</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!

##### Well-known Member
Show values as running total in Week Number base field:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Variety</td><td style=";">Orin</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Row Labels</td><td style=";">Sum of Dispatched Quantity Total TCE</td><td style=";">Sum of % of Estimate</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">8</td><td style="text-align: right;;">1800</td><td style="text-align: right;;">0.60%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">9</td><td style="text-align: right;;">3000</td><td style="text-align: right;;">1.60%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">10</td><td style="text-align: right;;">16979.82</td><td style="text-align: right;;">7.26%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">11</td><td style="text-align: right;;">19507.7</td><td style="text-align: right;;">13.76%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">12</td><td style="text-align: right;;">24220</td><td style="text-align: right;;">21.84%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">Grand Total</td><td style="text-align: right;;">65507.52</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br />