7. PivotTables and PivotCharts
7.6 Calculated Fields and Calculated Items
- 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
- Calculated Field… › Name
Net After Commission› Formula=Amount*0.98(fictional 2% payment commission) › Add › OK. - For AOV, first add a column
Order Count=1to tblOrders (each row = one order) and refresh. - 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
Calculated field row-by-row chalto asa khup students samajtat aani =Qty*Price lihun chukicha huge total miltat. Lakshat theva: calculated field sum var chalto. Row level calculation Table madhe calculated column madhe kara, aani ratio (AOV, margin %) sathi calculated field vapra.
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.