Calculated fields (also known as measures) are one of the most powerful features in Power Pivot. You will use them frequently in your data analysis. They are calculations you create for measuring a result relative to other factors pertinent to your analysis, such as total sales calculated for time, geography, organization, or product.
Some calculated fields are easy to create, such as those using a standard aggregation such as SUM or AVERAGE, and created by using the AutoSum feature in the Power Pivot window, or implicit calculated fields created in Excel by simply dragging a field into the Values area. Others can be more complex, such as those following relationships, or filtering results, requiring a formula you create using DAX.
Because there are different types of calculated fields, and you can create them in different places, it is important you understand what type will work best for you. For more detailed information, see Calculated Fields in Power Pivot.
To Create an Implicit Calculated Field in Excel
- Click on a PivotTable.
- In the PivotTable Fields list, drag a field into the Values area.
Implicit calculated fields can only use a standard aggregation (SUM, COUNT, MIN, MAX, DISTINCTCOUNT, or AVG), and must use the data format defined for that aggregation. In addition, implicit calculated fields can only be used by the PivotTable or chart for which they were created.
To Create a Calculated Field in the Power Pivot Window by using AutoSum
- Click on a column.
- Click Calculations> AutoSum, and then select an aggregation.
Calculated fields created by using AutoSum appear in the Calculations area immediately below the data column; however, you can move them to any other empty cell in the Calculations Area for that same table. Calculated fields created using AutoSum get a default name, however you can rename them in the formula bar. Calculated fields created in the Calculation Area are explicit.
To Create a Calculated Field in the Power Pivot Window by using the Calculation Area
- Click Home> View> Calculation Area.
- Click on an empty cell in the Calculation Area.
- In the formula bar, at the top of the table, enter a formula in this format <calculated field name>:<formula>
- Press Enter to accept the formula.
Calculated fields created in the Calculation Area are stored in the table selected, but can be used as a field by any PivotTable, PivotChart, or report. Calculated fields created using this method are explicit.
To Create a Calculated Field by using the Calculated Field Dialog Box in Excel
- In the Excel window, click Power Pivot> Calculations> Calculated Fields> New Calculated Field.
- In the Calculated Field dialog box, for Table name, click the down arrow, and then select the table you want the calculated field to be in.
The choice of table determines where the definition of the calculated field will be stored. It is not required for the calculated field to be stored with a table that the calculated field references.
- In Calculated Field Name, type a name.
The name of the calculated field must be unique, and you cannot use the same name that is used for any of the columns.
- In the Formula text box, position the cursor after the equal sign (=), and then enter a formula.
- Click Check Formula to validate.
- In Category, select a calculated field type.
The calculated field type does not affect how the formula calculates. It is for information purposes only.
- If the formula validates, click OK.
Calculated fields created by using the Calculated Field dialog box in Excel are stored in the table selected. You can later view and edit the calculated field in the Manage Calculated Fields dialog box in Excel or in the Calculation Area for the table in the Power Pivot window. Calculated fields created using this method are explicit.
Top of Page