Calculated Field

In an Excel PivotTable built on worksheet or other non-OLAP data, a calculated field adds a formula to the pivot table, producing a column the source data does not contain — a margin from revenue and cost, a rate from two counts, a per-unit figure from a total and a quantity. It lives inside the pivot, so it recalculates when the grouping or the filters change.

The essential property is what it computes from. A calculated field operates on the sums of the underlying fields for the group, not on each underlying row. Dividing summed revenue by a summed count therefore gives the group’s overall average — which is a weighted result — and not the average of each row’s individual ratio. The two differ whenever the rows carry different magnitudes, and the aggregate version is usually the one a reader expects from a group total.

This entry describes the calculated field of a classic Excel PivotTable. Microsoft documents that formulas for calculated fields operate on the sum of the underlying data for each field in the formula, that formulas cannot use cell references or defined names, and that calculated fields are not available in PivotTables based on OLAP source data. Measures written in DAX in a Power Pivot Data Model, calculated columns, and the calculated-field features of other spreadsheet and BI products are separate mechanisms with their own evaluation rules, which this entry does not describe.

That makes it the right tool for figures that must stay consistent with the summary around them, and the wrong tool for anything needing row-level logic before aggregation — that belongs in a column in the source data, where it can be checked row by row. When a calculated field and a formula outside the pivot disagree, check whether one divides group totals while the other averages row-level ratios. Then compare the included rows, filters, and refresh state.

Two practical cautions. A calculated field reflects whatever the pivot currently includes, so a filtered pivot yields a filtered figure and a copied pivot needs its numbers revalidated against what it now contains. And since the pivot reads a cached snapshot, a calculated field is as current as the last refresh — it recomputes from the cache, not from the source rows.

References

• Calculate values in a PivotTable
• Create a PivotTable to analyze worksheet data