Grand Total - Pivot

[loooser]

Is there a way to make the grand total in a pivot table calculate a % in stead of it summing all of the cells in a column> I am trying to get the Margin % column to work for the grand total row as well.

 

[markmzz]

Loooser,

Could you submit an example of what you want.

Markmzz

 

[loooser]

Job...................Sales Cost Margin Margin%
1......................10 5 5 50%
2 ......................20 5 15 75%
3 ......................50 10 40 80%
Grand total..........80 20 60 20%

 

[markmzz]

Loooser,

What is your version of Excel.

Markmzz

 

[loooser]

2007

 

[markmzz]

Loooser

Do the following (for Excel 2007):

1) With the pivot table selected, turn on the Options tab, choose Formulas, in the Tools group, and choose Calculated field on menu.

2) In the Insert calculated field dialog box, do the following:

In the Name box, type Marg/Sales

In the Formula box type =Margin/Sales

PS: Margin and Sales are the names of my fields.

And click OK

3) Finally, format the new column (%).

Markmzz

 

[loooser]

Oh I think I found the issue. There a couple of rows where there are no sales and #DIV/0 for margin % is showing and it is affecting the Grand Total line. Even though there is sales in the Grand Total line, the Margin % (in the Grand Total row) is showing #DIV/0.

 

[markmzz]

Oh I think I found the issue. There a couple of rows where there are no sales and #DIV/0 for margin % is showing and it is affecting the Grand Total line. Even though there is sales in the Grand Total line, the Margin % (in the Grand Total row) is showing #DIV/0.

Loooser,

Look at my pivot table:

<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</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="TEXT-ALIGN: center; COLOR: #161120">2</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="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right"></TD><TD>Valores</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD>Rótulos de Linha</TD><TD>Nº de Pedidos </TD><TD>Soma de Sales</TD><TD>Soma de Margin</TD><TD>Soma de Margin/Sales</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD>0-1999 </TD><TD style="TEXT-ALIGN: right">1.245 </TD><TD style="TEXT-ALIGN: right">R$ 12.633.850</TD><TD style="TEXT-ALIGN: right">R$ 5.822.365</TD><TD style="TEXT-ALIGN: right">46,09%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD>2000-3999 </TD><TD style="TEXT-ALIGN: right">199 </TD><TD style="TEXT-ALIGN: right">R$ 9.322.287</TD><TD style="TEXT-ALIGN: right">R$ 4.067.662</TD><TD style="TEXT-ALIGN: right">43,63%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD>4000-5999 </TD><TD style="TEXT-ALIGN: right">73 </TD><TD style="TEXT-ALIGN: right">R$ 5.722.076</TD><TD style="TEXT-ALIGN: right">R$ 2.558.500</TD><TD style="TEXT-ALIGN: right">44,71%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD>6000-7999 </TD><TD style="TEXT-ALIGN: right">25 </TD><TD style="TEXT-ALIGN: right">R$ 3.189.805</TD><TD style="TEXT-ALIGN: right">R$ 1.378.366</TD><TD style="TEXT-ALIGN: right">43,21%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD>8000-9999 </TD><TD style="TEXT-ALIGN: right">7 </TD><TD style="TEXT-ALIGN: right">R$ 998.623</TD><TD style="TEXT-ALIGN: right">R$ 572.073</TD><TD style="TEXT-ALIGN: right">57,29%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD>>10000 </TD><TD style="TEXT-ALIGN: right">3 </TD><TD style="TEXT-ALIGN: right">R$ 470.669</TD><TD style="TEXT-ALIGN: right">R$ 137.355</TD><TD style="TEXT-ALIGN: right">29,18%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD>Total geral </TD><TD style="TEXT-ALIGN: right">1.552 </TD><TD style="TEXT-ALIGN: right">R$ 32.337.311</TD><TD style="TEXT-ALIGN: right">R$ 14.536.322</TD><TD style="TEXT-ALIGN: right">44,95%</TD></TR></TBODY></TABLE>

Plan1​

Markmzz

 

[loooser]

how do I post a pic of my pivot? you can easly see the issue I am having.

 

[loooser]

DIST (All)
BRANCH 52
MANG'D BY (All)
BILLING CYCLE (All)


Branch JOB # TOTAL SALES TOTAL COST TOTAL MGN MARGIN %PERCENT
MIDWEST INGRED 952A10006 - 507 (507) #DIV/0!
MOKAN DIAL 552A32016 1,248 - 1,248 100.0%
MORRILL & JANE 452A32085 910 857 53 5.9%
MSSU 452A33062 10,525 - 10,525 100.0%
OMNIUM-WINFIELD 152A10003 849 - 849 100.0%
ORTHO SPORT CTR 952A10020 1,703 1,932 (229) -13.5%
PILOT #443 150A16013 2,065 1,987 78 3.8%
PINE RIDGE 1 452A31024 1,338 979 359 26.8%
PINE RIDGE 2 952A10008 3,100 2,499 601 19.4%
PINE RIDGE 4 452A31025 2,293 1,946 347 15.2%
PLAZA TOWER 052A10012 - 283 (283) #DIV/0!
POLO/RALPH #035 250A66085 719 240 479 66.7%
PROV MED - YORK 552A11120 - 418 (418) #DIV/0!
SAFE HAVEN 052A10001 500 - 500 100.0%
SALOMON SMITH B 452A32152 261 - 261 100.0%
SHERATON BOILER 952A10011 335 463 (128) -38.2%
SHERATON KC SPO 952A10012 3,452 3,675 (224) -6.5%
ST JOHN HOSP 352A22003 2,075 1,740 335 16.1%
TAMKO 752A30007 (0) - (0) 100.0%
THOMAS CUSTER H952A10015 1,260 894 366 29.1%
TPP INC 552A32019 503 - 503 100.0%
US CELLULAR 852A30009 5,084 3,195 1,890 37.2%
VA HOPSITAL 052A10016 24,482 14,658 9,824 40.1%
VINYLPLEX 652A30003 2,452 2,596 (144) -5.9%
Grand Total 166,411 40,795 25,616 #DIV/0!