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.

 

[loooser]

if you set sales to zero in one of the lines in your table, I think you will get the same #Div/0 error.

 

[markmzz]

Loooser,

I deleted data from a few rows of the field Margin and # DIV/0 has not occurred.

Probably the problem are in Sales field.

Did you see my pivot table?

Markmzz

 

[loooser]

Yes I see your table. If you take sales out of one of the cells then I thing you will get the same error. Remove sales only.

 

[markmzz]

Yes I see your table. If you take sales out of one of the cells then I thing you will get the same error. Remove sales only.

Loooser,

I recreate my PivotTable and delete some lines of field Sales and the error not occurred, see the example below:

<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></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></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></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></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></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD>Rótulos de Linha</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>Automóvel</TD><TD style="TEXT-ALIGN: right">R$ 734.555</TD><TD style="TEXT-ALIGN: right">R$ 308.235</TD><TD style="TEXT-ALIGN: right">41,96%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD>Avião</TD><TD style="TEXT-ALIGN: right">R$ 3.741.958</TD><TD style="TEXT-ALIGN: right">R$ 1.643.375</TD><TD style="TEXT-ALIGN: right">43,92%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD>Brontossauro</TD><TD style="TEXT-ALIGN: right">R$ 2.587.986</TD><TD style="TEXT-ALIGN: right">R$ 1.191.610</TD><TD style="TEXT-ALIGN: right">46,04%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD>Cavalo</TD><TD style="TEXT-ALIGN: right">R$ 5.038.664</TD><TD style="TEXT-ALIGN: right">R$ 2.274.753</TD><TD style="TEXT-ALIGN: right">45,15%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD>Elefante</TD><TD style="TEXT-ALIGN: right">R$ 2.777.588</TD><TD style="TEXT-ALIGN: right">R$ 1.397.548</TD><TD style="TEXT-ALIGN: right">50,32%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD>Girafa</TD><TD style="TEXT-ALIGN: right">R$ 3.764.935</TD><TD style="TEXT-ALIGN: right">R$ 2.053.523</TD><TD style="TEXT-ALIGN: right">54,54%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD>Peterodáctilo</TD><TD style="TEXT-ALIGN: right">R$ 3.492.042</TD><TD style="TEXT-ALIGN: right">R$ 1.433.309</TD><TD style="TEXT-ALIGN: right">41,05%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD>Tiranossauro</TD><TD style="TEXT-ALIGN: right">R$ 1.278.386</TD><TD style="TEXT-ALIGN: right">R$ 618.130</TD><TD style="TEXT-ALIGN: right">48,35%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD>Trem</TD><TD style="TEXT-ALIGN: right">R$ 2.297.734</TD><TD style="TEXT-ALIGN: right">R$ 1.127.778</TD><TD style="TEXT-ALIGN: right">49,08%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD>Zebra</TD><TD style="TEXT-ALIGN: right">R$ 4.788.170</TD><TD style="TEXT-ALIGN: right">R$ 2.485.608</TD><TD style="TEXT-ALIGN: right">51,91%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</TD><TD>Total geral</TD><TD style="TEXT-ALIGN: right">R$ 30.502.017</TD><TD style="TEXT-ALIGN: right">R$ 14.533.872</TD><TD style="TEXT-ALIGN: right">47,65%</TD></TR></TBODY></TABLE>

Plan4​

Try create the pivot table again.

Markmzz

 

[loooser]

Set cell B6 to zero in your template and do not change anything else and see what happens.

 

[markmzz]

Loooser,

I did another test and deletes all rows corresponding to the product Automóvel and the error occurred, then for a given item rows of the PivotTable you don't have data.

<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></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></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></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></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></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD>Rótulos de Linha</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>Automóvel</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">R$ 308.235</TD><TD style="TEXT-ALIGN: right">#DIV/0!</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD>Avião</TD><TD style="TEXT-ALIGN: right">R$ 3.741.958</TD><TD style="TEXT-ALIGN: right">R$ 1.643.375</TD><TD style="TEXT-ALIGN: right">43,92%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD>Brontossauro</TD><TD style="TEXT-ALIGN: right">R$ 2.587.986</TD><TD style="TEXT-ALIGN: right">R$ 1.191.610</TD><TD style="TEXT-ALIGN: right">46,04%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD>Cavalo</TD><TD style="TEXT-ALIGN: right">R$ 5.038.664</TD><TD style="TEXT-ALIGN: right">R$ 2.274.753</TD><TD style="TEXT-ALIGN: right">45,15%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD>Elefante</TD><TD style="TEXT-ALIGN: right">R$ 2.777.588</TD><TD style="TEXT-ALIGN: right">R$ 1.397.548</TD><TD style="TEXT-ALIGN: right">50,32%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD>Girafa</TD><TD style="TEXT-ALIGN: right">R$ 3.764.935</TD><TD style="TEXT-ALIGN: right">R$ 2.053.523</TD><TD style="TEXT-ALIGN: right">54,54%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD>Peterodáctilo</TD><TD style="TEXT-ALIGN: right">R$ 3.492.042</TD><TD style="TEXT-ALIGN: right">R$ 1.433.309</TD><TD style="TEXT-ALIGN: right">41,05%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD>Tiranossauro</TD><TD style="TEXT-ALIGN: right">R$ 1.278.386</TD><TD style="TEXT-ALIGN: right">R$ 618.130</TD><TD style="TEXT-ALIGN: right">48,35%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD>Trem</TD><TD style="TEXT-ALIGN: right">R$ 2.297.734</TD><TD style="TEXT-ALIGN: right">R$ 1.127.778</TD><TD style="TEXT-ALIGN: right">49,08%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD>Zebra</TD><TD style="TEXT-ALIGN: right">R$ 4.788.170</TD><TD style="TEXT-ALIGN: right">R$ 2.485.608</TD><TD style="TEXT-ALIGN: right">51,91%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</TD><TD>Total geral</TD><TD style="TEXT-ALIGN: right">R$ 29.767.462</TD><TD style="TEXT-ALIGN: right">R$ 14.533.872</TD><TD style="TEXT-ALIGN: right">48,82%</TD></TR></TBODY></TABLE>




I don't see how to solve "the problem" without removing this item from the PivotTable.

Markmzz

 

[loooser]

But, how is your grand total margin % still working? Mine does not. You are still getting 48.82% even though you have a #Div/0 in one of your cells above.

 

[markmzz]

Loooser,

Yes, should not occur the error in the Grand Total.

So my suggestion is to re-create the pivot table.

Markmzz

 

[loooser]

This is madening. I built the pivot over again and I have the same issue. UGH!

 

[markmzz]

This is madening. I built the pivot over again and I have the same issue. UGH!

Loooser,

I'm sorry, but if my suggestion does not resolve the problem. I'm not seeing another alternative.

So, let's wait a suggestion from another user of the Forum.

Markmzz