PivotdrillCalculated field
Click in the pivot, go to PivotTable Analyze, Fields, Items & Sets, Calculated Field, give it a name and a formula such as =Revenue - Cost, then Add and OK. The formula is applied to the summed fields in each cell, so ratios like =Revenue/Units are correct but averages of averages are not.
Syntax
PivotTable Analyze > Fields, Items & Sets > Calculated Field > Name, Formula = Revenue - Cost > Add| Part | What it is |
|---|---|
Name | The new field's name; appears in the field list |
Formula | Uses source field names; wrap names with spaces in single quotes, as in ='Unit Price'*Units |
Fields list | Double-click a field to insert it into the formula |
Examples
Calculated Field: Commission = Revenue * 0.05
Rows: Rep. Values: Sum of Revenue, Sum of CommissionResult: Each rep's revenue and a 5% commission next to it; the total is 5% of total revenue.
Calculated Field: Price per Unit = Revenue / Units
Rows: Product. Values: Sum of Price per UnitResult: Total revenue divided by total units per product, a correct weighted average price.
Calculated Field: Margin % = (Revenue - Cost) / Revenue
Value Field Settings > Number Format > PercentageResult: Margin for each row and a correct overall margin on the Grand Total.
Common mistakes
- The Grand Total of a calculated field is not the sum of the rows: it is the formula applied to the totals, which is right for ratios but confusing for products and conditions.
- =IF(Units>100, Revenue*0.1, 0) gives wrong results: the IF tests the summed units per cell, not each source row; add a helper column in the source instead.
- The formula shows #NAME?: a field name with a space is not quoted; write 'Unit Price'.
- Calculated Field is grayed out: the pivot uses the Data Model; create a measure instead.
Practice Calculated field
These drills check your answer on live data, free, no account needed.
- Commission checkYour manager pays commission on revenue per rep. List every Rep with total Revenue, highest first.
- Units and revenue togetherShow both total Units and total Revenue for each Product.
Questions
How do I delete a calculated field?
Open Calculated Field, pick the name from the dropdown, click Delete, then OK.
Can a calculated field use COUNT?
Not meaningfully. Any field in the formula is treated as its sum. For counts and averages use a helper column, a Data Model measure, or the Average function on the value itself.
Why can't I use a calculated field with grouped dates?
You can; the restriction is on calculated items, not fields.
Related
- Calculated itemCreate a calculated item to combine or compare row items, for example Americas = East + West. Where the command hides, why it is grayed out, double counting.
- Data Model pivot tableWhat Add this data to the Data Model does: relate several tables without lookups, get Distinct Count and write DAX measures. Also what you lose by using it.
- Sum, Count and Average in a pivot tableChange a pivot table value from Count to Sum or Average in Value Field Settings, show total, count and average side by side, and fix a column that counts.
- Calculated field in Google SheetsAdd a calculated field to a Google Sheets pivot table: Values, Add, Calculated field, then =Revenue-Cost summarized by SUM, or =SUM(Revenue)/SUM(Units).