Measures (also known as calculated fields) 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 measures 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 measures 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 measures, 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 Measures in Power Pivot.
To Create an Implicit Measure in Excel
-
Click on a PivotTable.
-
In the PivotTable Fields list, drag a field into the Values area.
Implicit measures 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 measures can only be used by the PivotTable or chart for which they were created.
To Create a Measure in the Power Pivot Window by using AutoSum
-
Click on a column.
-
Click Calculations> AutoSum, and then select an aggregation.
Measures 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. Measures created using AutoSum get a default name, however you can rename them in the formula bar. Measures created in the Calculation Area are explicit.
To Create a Measure 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 <measure name>:<formula>
-
Press Enter to accept the formula.
Measures created in the Calculation Area are stored in the table selected, but can be used as a field by any PivotTable, PivotChart, or report. Measures created using this method are explicit.
To Create a Measure by using the Measure Dialog Box in Excel
-
In the Excel window, click Power Pivot> Calculations> Measures> New Measure.
-
In the Measure dialog box, for Table name, click the down arrow, and then select the table you want the measure to be in.
The choice of table determines where the definition of the measure will be stored. It is not required for the measure to be stored with a table that the measure references.
-
In Measure Name, type a name.
The name of the measure 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 measure type.
The measure type does not affect how the formula calculates. It is for information purposes only.
-
If the formula validates, click OK.
Measures created by using the Measure dialog box in Excel are stored in the table selected. You can later view and edit the measure in the Manage Measures dialog box in Excel or in the Calculation Area for the table in the Power Pivot window. Measures created using this method are explicit.
