Ravindra BagaleCourses & study guides

7. PivotTables and PivotCharts

7.6 Calculated Fields and Calculated Items

By Last updated:

  • Calculated Field – a new value built from other fields: PivotTable Analyze › Calculations › Fields, Items, & Sets › Calculated Field…
  • Calculated Item – a new item inside a row/column field (e.g. "Pune + Nashik"): Fields, Items, & Sets › Calculated Item… (select an item of that field first).

Steps in Excel – commission and AOV

  1. Calculated Field… › Name Net After Commission › Formula =Amount*0.98 (fictional 2% payment commission) › Add › OK.
  2. For AOV, first add a column Order Count = 1 to tblOrders (each row = one order) and refresh.
  3. Calculated Field… › Name AOV › Formula =Amount/'Order Count' › OK. Format as ₹.

Important: a calculated field works on the sums of fields, not row by row. =Amount/'Order Count' = Sum of Amount ÷ Sum of Order Count – correct for AOV. But a formula like =Qty*Unit Price would multiply the sums, which is wrong – do row-level maths in the source Table instead.

Calculated items can't be used when the same field has grouped items, and they can make totals confusing; prefer grouping (7.5) where possible.

Ravindra Bagale's Tip

Many students think a calculated field works row by row, write =Qty*Price, and get a wrong, huge total. Remember: a calculated field works on the sums. Do row-level calculations in a calculated column in the Table, and use a calculated field for ratios (AOV, margin %).

Practice task

Add calculated fields Net After Commission and AOV (with an Order Count column). Compare AOV per city with a manual SUMIFS/COUNTIFS check.