Calculation Area in the Power Pivot Add-in

When using the Power Pivot add-in to modify a data model, you might want to use the Calculation Area to create and manage measures.

A measure (also known as a calculated field) is a new item that you create in a model that is not part of the original source data. It appears as a field in your PivotTable. Examples of measures include net values (such as net profit), averages, and medians.

The Calculation Area is a grid at the bottom of every table in the add-in that you use to create and manage calculated fields.

1. Start Power Pivot in Microsoft Excel 2013 add-in and open the Power Pivot window.

2. Click Home > View > Calculation Area. The Calculation Area is on by default. If the grid disappears, click Calculation Area so that it reappears.

3. Click Advanced > Show Implicit Measures to view the measures that Excel creates automatically when you add fields to the Values area in a PivotTable Fields list.

   An implicit measure is one that gets created through PivotTable interaction. It is created when you drag a numeric value, such as Sales Amount, to the Values area of a PivotTable. Excel automatically sums the values, resulting in a new measure named Sum of Sales Amount.

   Showing implicit measures is useful because it lets you delete any that you no longer need. Once Excel creates an implicit measure, it stays in the data model until you delete it.

   To find implicit measures, click Find on the Power Pivot ribbon if you know the name of the field. Otherwise, go to a tab that contains numeric data that you might have counted or summed. For example, if Sales Amount is located in the FactInternetSales table, view the Calculation Area of that table to see its calculated fields.

   Remember that implicit measures can be referenced in other calculations. If you delete an implicit measure that is used in another formula, that formula will be invalidated.

   Tip:  You can put a measure anywhere in the grid. It doesn't matter which tab, table, or column is active. Calculated fields are globally available throughout the model. For simplicity, you might want to choose a fact table (or another table that contains mostly numeric data that you plan to aggregate) and put all of your measures there. See Create a Measure for more information.

Measures

A measure is a formula that is created specifically for use in a PivotTable, PivotChart, or Power View report. Calculated fields can be based on standard aggregation functions, such as COUNT or SUM, or you can define your own formula by using DAX in the Power Pivot add-in. See Calculated Fields in Power Pivot for details.

KPIs

A KPI is based on a specific measure and is designed to help evaluate the current value, status, and trend of a metric. The KPI gauges the performance of the value, defined by a base measure, against a target value. You can extend a base measure to a KPI. For more information about KPIs, see Key Performance Indicators (KPIs) in Power Pivot.