Feb 27 2023 06:55 PM
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.
Feb 27 2023 10:53 PM
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.