Create a Measure
This topic shows you how to create a measure based on data in the DAX sample workbook. This workbook includes bike-related data from the AdventureWorks database. For information about where to get the sample workbook, see Get Sample Data for PowerPivot. For more information about formulas, see Building Formulas for Calculated Columns and Measures.
Understanding Measures
A measure is a formula that is created specifically for use in a PivotTable (or PivotChart) that uses PowerPivot data. Measures can be based on standard aggregation functions, such as COUNT or SUM, or you can define your own formula by using DAX. A measure is used in the Values area of a PivotTable. If you want to place calculated results in a different area of a PivotTable, use a calculated column instead (Create a Calculated Column).
When you create a measure, you associate it with a table in the workbook; the measure definition is saved with this table. It appears in the PowerPivot Field List and is available to all users of the workbook.
Creating and Editing Measures
Before you create a measure, you must first add a PivotTable or Pivot Chart to your PowerPivot workbook. When you add the measure, the formula is evaluated for each cell in the Values area of the PivotTable. Because a result is created for each combination of row and column headers, the result for the measure can be different in each cell.
After you have added a PivotTable or Pivot Chart to your PowerPivot workbook, use the Measure Settings dialog box to add a measure that contains a formula. The formula defines a sum, average, or other calculation using the columns and tables in the PowerPivot window. You create standard aggregations the same way that you do in Excel—by dragging fields to the Values field area and then choosing one of the standard aggregation methods: COUNT, SUM, AVERAGE, MIN, or MAX. Custom aggregations are discussed in the next section.
The measure that you create can be used in more than one PivotTable or PivotChart. The name of the measure must be unique within a workbook, and you cannot use the same name that is used for any of the columns in a workbook.
Example: Creating a Measure that uses a Custom Aggregation
In this example, you will create a custom aggregation that uses one of the new DAX aggregation functions, SUMX, and the function ALL, which in this case returns all values from a column regardless of that column's context. The example uses the following columns from the DAX sample workbook:
DateTime[CalendarYear]
ProductCategory[ProductCategoryName]
ResellerSales_USD[SalesAmount_USD]
The example uses a PivotTable that has CalendarYear as a row label and ProductCategoryName as a column label; SalesAmount_USD is used in the measure formula. The example answers the question: what percentage of total sales from 2001-2004 did each year and product category contribute? This enables you to see, for example, what percentage of the total was contributed by bike sales in 2003. In order to answer this question, we use the following measure formula:
=SUMX(ResellerSales_USD, ResellerSales_USD[SalesAmount_USD])/SUMX(ALL(ResellerSales_USD), ResellerSales_USD[SalesAmount_USD])
The formula is constructed as follows:
The numerator, SUMX(ResellerSales_USD, ResellerSales_USD[SalesAmount_USD]), is the sum of the values in ResellerSales_USD[SalesAmount_USD] for the current cell in the PivotTable. Having the context of CalendarYear and ProductCategoryName means that this value will be different for each combination of year and product category. For example, the total number of bikes sold in 2003 is different from the total number accessories sold in 2004.
For the denominator, you start by specifying a table, ResellerSales_USD, and use the ALL function to remove all context on the table. This ensures that the value will be the same for each combination of year and product category: the denominator will always be total sales from 2001-2004.
You then use the SUMX function to sum the values in the ResellerSales_USD[SalesAmount_USD] column. In other words, you get the sum of ResellerSales_USD[SalesAmount_USD] for all reseller sales.
Note
In Windows Vista and Windows 7, features in the PowerPivot window are available on a ribbon, which is discussed in this topic. In Windows XP, features are available from a set of menus. If you are using Windows XP and want to see how the menu commands relate to the ribbon commands, see The PowerPivot UI in Windows XP.
To create a measure that uses a custom aggregation
In the PowerPivot window, click the Home tab, and in the Reports group, click PivotTable.
In the Create PivotTable dialog box, verify that New Worksheet is selected, and click OK.
PowerPivot creates a blank PivotTable in a new Excel worksheet and displays the PowerPivot Field List on the right side of the workbook.
In the Excel window, use the PowerPivot Field List to add columns to the PivotTable:
Find the DateTime table, and drag the column CalendarYear to the Row Labels area of the PivotTable.
Find the ProductCategory table, and drag the column ProductCategoryName to the Row Labels area of the PivotTable.
In the Excel window, on the PowerPivot tab, in the Measures group, click New Measure.
In the Measure Settings dialog box, for Table name, click the down arrow, and select ResellerSales_USD from the dropdown list.
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.
For Measure Name (All Pivot Tables), type AllResSalesRatio.
This name is used as an identifier for the measure; therefore, it must be unique within the workbook, and cannot be changed.
For Custom Name (This PivotTable), type All Reseller Sales Ratio.
This name is used only within the current Pivot Table, for display purposes. For example, you might reuse the measure, AllResSalesRatio, in other PivotTables but give it a different name, or use a different language.
In the Formula text box, position the cursor after the equal sign (=).
Type SUMX, and then a parenthesis.
=SUMX(As you type, the tooltip beneath the Formula textbox indicates that the SUMX function requires two arguments: the first argument is a table or an expression that returns a table, and the second argument is an expression that provides the numbers that can be summed.
Type Res, and then select ResellerSales_USD from the list, and press TAB.
The column name is inserted into the formula as follows:
=SUMX(ResellerSales_USDType a comma.
The tooltip updates to show that the next argument required is expression. An expression can be a value, a reference to a column, or some combination of those. For example, you could create an expression that sums two other columns. For this example, you will provide the name of a column that contains the sales amount for each reseller.
Type the first few letters of the name of the table that contains the column that you want to include. For this example, type Res, and select the column ResellerSales_USD[SalesAmount_USD] from the list.
Press TAB to insert the column name into the formula, and add a closing parenthesis, as shown here:
=SUMX(ResellerSales_USD, ResellerSales_USD[SalesAmount_USD])Type a forward slash, and then type or copy and paste the following code into the Measure Settings dialog box:
SUMX(ALL(ResellerSales_USD), ResellerSales_USD[SalesAmount_USD])Note how the ALL function is nested inside the SUMX function. The entire formula now appears as follows:
=SUMX(ResellerSales_USD, ResellerSales_USD[SalesAmount_USD])/SUMX(ALL(ResellerSales_USD), ResellerSales_USD[SalesAmount_USD])Click Check formula.
The formula is checked for syntax or reference errors. Resolve any errors that might have been found, such as a missing parenthesis or comma.
Click OK.
The measure now populates the PivotTable with values for each combination of calendar year and product category.
Format the table:
Select the data in the PivotTable, including the Grand Total row.
On the Home tab, in the Number group, click the percent button (%) once, and then click the increase decimal button (<- .0 .00) twice.
The finished table should appear as below. You can now see the percentage of total sales for each combination of product and year. For example, bike sales in 2003 accounted for 31.71% of all sales from 2001-2004.
All Reseller Sales |
Column Labels |
|
|
|
|
Row Labels |
Accessories |
Bikes |
Clothing |
Components |
Grand Total |
2001 |
0.02% |
9.10% |
0.04% |
0.75% |
9.91% |
2002 |
0.11% |
24.71% |
0.60% |
4.48% |
29.90% |
2003 |
0.36% |
31.71% |
1.07% |
6.79% |
39.93% |
2004 |
0.20% |
16.95% |
0.48% |
2.63% |
20.26% |
Grand Total |
0.70% |
82.47% |
2.18% |
14.65% |
100.00% |
Edit an Existing Measure
To view the definition of an existing measure, you use the PowerPivot Field List. The PowerPivot Field List contains a list of all tables in the current PowerPivot window, including columns of raw data, calculated columns, and any measures that you might have defined. You can right-click on the definition of any measure and select Edit formula to open a dialog box that lets you view and modify the measure definition.
To view and change an existing measure
In the Excel window, click anywhere in the PivotTable or PivotChart area to display the PowerPivot Field List.
In the PowerPivot Field List, locate the table that contains the measure that you created.
Each table can contain base columns, calculated columns and measures. Measures are indicated by a small calculator icon to the right of the measure name.
For this example, right-click SumAmtByReseller, and click Edit Formula.
In the Measure Settings dialog box, edit the formula.
You can also change the measure name or custom name, and the associated table.
See Also
Other Resources
Creating and Working with Calculations