Calculated Fields and Calculated Items — and their traps

1. Calculated Field

PivotTable Analyze → Fields, Items & Sets → Calculated Field.

Name: Avg Price, Formula: =Amount/Qty → Add.

Correct — because a weighted average price is total amount ÷ total qty.

2. The rule: calculated fields work on SUMS

Excel first sums each field for the pivot cell, then applies your formula. Not row by row.

That's fine for ratios (Amount/Qty, Profit/Amount). It's wrong for anything that must be calculated per row.

Trap example — commission: 5% on orders above 1,00,000, else 2%. Calculated field: =IF(Amount>100000, Amount*5%, Amount*2%)

For North, the pivot sees Sum of Amount = 5,09,000 → 5% → 25,450. The truth row by row: 3 orders above 1 lakh (3,83,000 × 5% = 19,150) + 3 smaller (1,26,000 × 2% = 2,520) = 21,670.

Fix: add a Commission column in the source table (=IF([@Amount]>100000,[@Amount]*5%,[@Amount]*2%)) and sum that — or use a DAX measure with SUMX (Module 4).

Same trap: =Qty*Rate becomes Sum(Qty) × Sum(Rate) — nonsense.

3. Other calculated field limits

• Only fields from the source; can't reference cells or other pivots.
• Count/Average of a calculated field isn't possible — it's always sum-based.
• To see all formulas: Fields, Items & Sets → List Formulas.

4. Calculated Item

A new item inside a field. Click a Region cell → Calculated Item → Name North+West, Formula =North+West.

Result: a new row "North+West" = 8,11,000. Problems:

• The grand total now counts North and West twice → 18,81,000 instead of 10,70,000.
• You can't use it on grouped fields, and many layouts become slow.
• Combined with other fields, it creates many meaningless combinations.

Better: a manual group (Lesson 3) or a "Zone" column in the source/dimension table.

5. Decision guide

Common mistakes

IF or multiplication in calculated fields expecting row-level results. Leaving a calculated item in a report with a doubled grand total. Forgetting calculated fields exist when numbers look strange (check List Formulas).