It’s unbelievable, but Excel has this bizarre bug (documented and assumed here) that makes Calculated Fields in a Pivot Table useless.

Suppose you have a table with columns PRICE (unit price) and QTY (quantity sold). When you create a Calculated Field with the formula PRICE * QTY, and let it to use the default aggregation (SUM), you expect that they do SUM(PRICE * QTY), right?

But what the pivot table really calculates is SUM(PRICE) * SUM(QTY):

In other words, if you sold 2 units at 10 USD each, and 3 units at 20 USD each, you expect that the Calculated Field sums (2 * 10) + (3 * 20) = 80USD. But what it does is (2+3) * (10+20) = 150USD.

Unbelievable.