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

AnAppleADay

New Member
Joined
Jun 11, 2018
Messages
6
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
 

AnAppleADay

New Member
Joined
Jun 11, 2018
Messages
6
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

Well-known Member
Joined
Sep 19, 2005
Messages
5,112
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:

<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 /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Variety</td><td style=";">Dispatched Quantity Total TCE</td><td style=";">% of Estimate</td><td style="text-align: right;;"></td><td style="text-align: right;;"></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;">2</td><td style=";">English Cox</td><td style="text-align: right;;">600</td><td style="text-align: right;;">0.001</td><td style="text-align: right;;"></td><td style=";">Apples</td><td style=";">SALES ESTIMATE</td><td style=";">English Cox</td><td style="text-align: right;;">600,000.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Orin</td><td style="text-align: right;;">1,800.00</td><td style="text-align: right;;">0.006</td><td style="text-align: right;;"></td><td style=";">Apples</td><td style=";">SALES ESTIMATE</td><td style=";">Orin</td><td style="text-align: right;;">300,000.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">English Cox</td><td style="text-align: right;;">6,000.00</td><td style="text-align: right;;">0.01</td><td style="text-align: right;;"></td><td style=";">Apples</td><td style=";">SALES ESTIMATE</td><td style=";">Golden </td><td style="text-align: right;;">300,000.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Orin</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;">0.003333333</td><td style="text-align: right;;"></td><td style="text-align: right;;"></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;">6</td><td style=";">English Cox</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;">0.001666667</td><td style="text-align: right;;"></td><td style="text-align: right;;"></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;">7</td><td style=";">Orin</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;">0.003333333</td><td style="text-align: right;;"></td><td style=";">Variety</td><td style=";">Orin</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;">8</td><td style=";">English Cox</td><td style="text-align: right;;">8,070.00</td><td style="text-align: right;;">0.01345</td><td style="text-align: right;;"></td><td style="text-align: right;;"></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=";">Orin</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;">0.003333333</td><td style="text-align: right;;"></td><td style=";">Row Labels</td><td style=";">Sum of Dispatched Quantity Total TCE</td><td style=";">Sum of % of Estimate</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">Orin</td><td style="text-align: right;;">8,070.00</td><td style="text-align: right;;">0.0269</td><td style="text-align: right;;"></td><td style=";">8</td><td style="text-align: right;;">1800</td><td style="text-align: right;;">0.60%</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">English Cox</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;">0.001666667</td><td style="text-align: right;;"></td><td style=";">9</td><td style="text-align: right;;">3000</td><td style="text-align: right;;">1.00%</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">English Cox</td><td style="text-align: right;;">2,239.94</td><td style="text-align: right;;">0.003733233</td><td style="text-align: right;;"></td><td style=";">10</td><td style="text-align: right;;">16979.82</td><td style="text-align: right;;">5.66%</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">Orin</td><td style="text-align: right;;">2,264.15</td><td style="text-align: right;;">0.007547167</td><td style="text-align: right;;"></td><td style=";">11</td><td style="text-align: right;;">19507.7</td><td style="text-align: right;;">6.50%</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">Orin</td><td style="text-align: right;;">2,285.67</td><td style="text-align: right;;">0.0076189</td><td style="text-align: right;;"></td><td style=";">12</td><td style="text-align: right;;">24220</td><td style="text-align: right;;">8.07%</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">Orin</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;">0.003333333</td><td style="text-align: right;;"></td><td style=";">Grand Total</td><td style="text-align: right;;">65507.52</td><td style="text-align: right;;">0.2183584</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 /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E2</th><td style="text-align:left">=D2/VLOOKUP(<font color="Blue">C2,$I$2:$J$4,2,0</font>)</td></tr></tbody></table></td></tr></table><br />

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

AnAppleADay

New Member
Joined
Jun 11, 2018
Messages
6
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

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

Worksheet Formulas
CellFormula
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

Well-known Member
Joined
Sep 19, 2005
Messages
5,112
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 />
 

Forum statistics

Threads
1,082,603
Messages
5,366,578
Members
400,904
Latest member
ndaines meriabi

Some videos you may like

This Week's Hot Topics

Top