Calculated Fields and Custom Metrics

Pivot Tables can calculate totals, averages, and counts automatically. But sometimes you need a custom calculation that doesn't exist in your source data. In this chapter, you'll create calculated fields directly inside a Pivot Table to build your own metrics.

A Calculated Field allows you to create a new formula inside a Pivot Table using existing fields from the dataset. This is useful when:

• You need profit but only have revenue and cost;
• You need commission based on sales;
• You need percentage-based adjustments;
• You want to simulate business rules.

A calculated field does not change the source data. It exists only inside the Pivot Table.

Creating a Calculated Field

To insert a Calculated Field:

1. Click inside the Pivot Table;
2. Open the PivotTable Analyze tab;
3. Click Fields, Items & Sets;
4. Select Calculated Field;
5. Enter:
   • A name for the new field;
   • A formula using existing field names.
6. Click Add or OK.

The new field becomes available in the Pivot Table and is added to the Values area.

Create a Pivot Table with:

• Rows: Category;
• Values: Sum of Revenue;
• Values: Sum of Cost;

Create a Calculated Field:

• Name: Profit;
• Formula: = Revenue - Cost.

Create another Calculated Field:

• Name: Profit Margin;
• Formula: = (Revenue - Cost) / Revenue.