Excel Pivot table grand total

New Contributor

Hello, I have created two pivot tables based on different tables as below picture  and I have  a calculated field unit price =total amount / qtty.

The question is why the grand total in first table  can be correctly calculated but failed in second table since both table have either a column or a row has errors.

Thanks for the help.


1 Reply


Calculated fields work on the row level

In the first pivot table both rows for A and B return a number. Then the Grand Total will calculate as expected.

In the second table, the row for A returns a #DIV0! error (180/0) Then the Grand Total will error out as well.


Put the Area in the row field and the Department in the column area and you'll see that the first PT grand total errors out since the total for Y will be (140/0). The second pivot table will work fine.