Calculated Fields and Calculated Items

AnalyticsModelling & Forecasting8 min readMicrosoft 365 for Windows

The problem

A commercial team wants the average net price of each product in each region. The sales log has a column called Net Price, so the obvious move is to drop it in the Values area. The result is a number that looks right and is wrong: Excel offers Average of Net Price, the simple mean of the transactions, and treats a transaction of 20 units and one of 4,000 units as equal. What the team needs is revenue divided by volume, the weighted price. That number is not in the source list as a column, and it cannot be obtained by changing the summary function.

A second request arrives the same week: show Velorin and Cardexa together as one line, next to the individual products, without touching the source. A PivotTable can do both. The first is a calculated field, the second a calculated item. They look alike in the menu and behave very differently, and the second one is a well-known trap.

Short theory

A PivotTable normally summarizes the fields that exist in the source. A calculated field is a new field built from a formula that uses other fields, and it behaves in the report like any other value field. A calculated item is a new item added inside an existing field, built from a formula that uses other items of the same field, such as a new product that is the sum of two products.

The key to the calculated field is how it is computed. Excel first sums each field it uses, for the cell it is calculating, and then applies the formula to those sums. Microsoft documents this: calculated-field formulas operate on the sum of the underlying data. A formula such as net sales divided by units is therefore a ratio of sums, which is what a weighted average is. A formula that uses a field as if it were a single record, such as a minimum or an average of a field, is applied to a sum and returns a meaningless number.

The formulas have limits that come from the same logic. They can use constants, the fields of the report and the usual operators and functions that do not need cell references. They cannot refer to worksheet cells or to named ranges, and they cannot refer to totals such as a grand total. They are not available in PivotTables based on an OLAP source. In a PivotTable built on the Data Model, which is the subject of an article of its own, the equivalent tool is a measure written in DAX, and Microsoft's page on when to use calculated columns and calculated fields describes the choice.

Both tools live under PivotTable Analyze, Fields, Items & Sets. The same menu holds List Formulas, which creates a new sheet with every calculated field and item of the PivotTable, a useful audit when a report has been passed through several hands.

Steps in Excel

A calculated field: weighted average price

Build a PivotTable on tblSales with Product in Rows and Region in Columns, as in the first article of this category. Click any cell of the report, open Fields, Items & Sets and choose Calculated Field. The dialog has three parts: a Name box, a Formula box and a list of Fields.

Type a name, for example WeightedPrice. In the Formula box, replace the proposed = 0 with the formula. Field names that contain a space must be placed in single quotes:

='Net Sales'/Units

You can type the names or select a field in the list and click Insert Field. Click Add and then OK. Excel adds the field to the Values area with a name such as Sum of WeightedPrice. Rename it by clicking the header and typing, for instance, Net price (weighted); the new name must differ from every field name in the source. Set a number format with two decimals in Value Field Settings.

The Calculated Field sheet of the companion file shows what you must obtain. The first block holds the net sales by product and region (rows 4 to 10), the second the units (rows 14 to 20) and the third the quotient in rows 24 to 30, with the total of the report at 47.36 in F30. The fourth block, rows 34 to 40, shows what Average of Net Price returns instead. For Velorin the weighted price is 40.39 and the simple average 41.09; for Neurolin 115.01 against 116.15. The difference is small in percentage but systematic: large orders carry the volume discount, so they pull the weighted price down. Cell F42 holds the difference for the whole log, 0.26, the weighted price being lower.

Net Sales divided by Units, both summed first: a calculated field is a ratio of sums, a true weighted price.

A guard against empty combinations

If a combination of product and region has no units, the quotient divides by zero. A PivotTable shows the error in the cell, and a single error spoils the look of the whole report. Use IF inside the formula:

=IF(Units=0,0,'Net Sales'/Units)

The same guard is in the companion file, in the cells of the weighted price block. Note that the formula returns zero, not a blank: a calculated field must return a number.

A calculated item: two products as one

Select a product label in the report, open Fields, Items & Sets and choose Calculated Item. The dialog opens on the Product field, with the items in a list. Name the new item PrimaryCare and build the formula by selecting items:

=Velorin+Cardexa

Click Add and OK. A new row appears in the report with the sum of the two products for every value field in the Values area: net sales and units, if both are there. In the companion file, the Calculated Item sheet reproduces it in row 8, with 1,290,076.67 across all regions in F8.

Now look at the grand total. Excel adds the new item to the total as if it were a product, so the report states a total of 8,376,547.82 (cell F10) instead of the correct 7,086,471.15 (F11). The overstatement of 1,290,076.67, in row 12, is exactly the value of the item. Every row total and column total that includes the item is wrong in the same way.

PrimaryCare is counted twice: once in its two products, once as an item, so the grand total is inflated.

This is the reason why many users distrust calculated items. The remedies are limited. You can switch off the totals that include the item, on the Design tab under Grand Totals, and compute the correct total elsewhere, for instance with a formula that reads the report. You can avoid the item and group the products instead, as in the grouping article, which produces a subtotal without a duplicate. Or you can add a family column to the source table and use it as a field.

There are two further restrictions on calculated items. They cannot be created in a field that has grouped items: Excel refuses, and you must ungroup first. And when a calculated item and a calculated field meet in the same cell, the Solve Order command, under the same menu, decides which formula is applied first. If a result looks odd, List Formulas and the solve order are the first things to look at.

Constants in formulas

A calculated field formula cannot read a cell, so any rate has to be typed into the formula. Suppose the distributor fee is 5% and the field is ='Net Sales'*0.95. The number lives inside the field, where nobody sees it, and if the fee changes, every report that uses the field must be edited. The Fixed Rate sheet shows it: the fee is in B1, column C follows it, while column D, which mimics the typed constant, does not. If you need a rate that is maintained in a cell, add a calculated column to the source table, which can read any cell, and use that column in the PivotTable.

Change the fee in B1 and column C follows; column D, like a rate typed into a calculated field, does not.

Which tool for which job

A short guide, offered as opinion with high confidence. Use a calculated field when the result is a ratio or a difference of fields that make sense when summed: price as revenue over volume, a margin as a difference, a rate as a quotient. Use a calculated column in the source table when the value must be computed record by record, when it needs a cell parameter or when you need a statistic of single transactions. Use a measure in the Data Model for anything that must be reused or that crosses several tables. Avoid calculated items for anything that ends up in a total.

Common mistakes

Using Average of Net Price where a weighted price is needed.
Writing a field name with spaces without quotes. Excel answers with an error and a formula that looks right.
Using MIN, MAX or AVERAGE of a field in a calculated field. The functions act on a sum.
Leaving the default name, which produces headers such as Sum of Field1 in a management report.
Reading the total of a report with a calculated item without checking it against the source.
Trying to create a calculated item on a grouped field.
Typing a rate into a formula and forgetting it is there.