Create a Measure in a PivotTable or PivotChart
Looking for help with Power Pivot in Excel 2013? Go to Power Pivot Help on Office.com.
A measure is a calculation that you create for the purpose of measuring an outcome or result relative to other factors that are pertinent to the analysis, such as time, geography, organization, or product characteristics. There are several ways to create a measure, but depending on how you plan to use the measure, not all approaches are equally effective. Specifically, if you are creating a workbook for use as a data model in a reporting application, be sure to create explicit measures only, as described in the sections below.
Creating measures for use in data models
Measures that you create in a PowerPivot workbook can be used in reports that you build in Power View and other reporting applications, coexisting with other measures or calculations that you create right in the report. For a measure to appear in the report as a predefined calculation, you must create it in a PowerPivot workbook as an explicit measure. An explicit measure is one that you create manually. It contrasts with implicit measures that Excel generates for you when you add fields to the Values area of a PivotTable.
Creating measures for use in Excel
When you create a measure for use in Excel, you must first add a PivotTable or Pivot Chart to your PowerPivot workbook. You can then create measures using any of the following approaches:
Create an implicit measure by dragging a field from the PowerPivot Field List into the Values area. If you drag a numeric field, the implicit measure is calculated using a SUM aggregation. If you drag a text field, the measure is calculated using a COUNT aggregation. You can edit an implicit measure to change the calculation to a different aggregation, such as MIN, MAX, or DISTINCTCOUNT.
Note
Implicit measures are easy to create, but have more limitations than explicit measure. Implicit measures cannot be renamed, moved, or used in other PivotTables or charts in the workbook. Furthermore, because implicit measures are based on an existing field, deleting that field also deletes related implicit measure. Finally, implicit measures can only use the data format that is built into the aggregation; they do not support the wide range of data formats that are available to explicit measures.
Create an explicit measure manually, using the New Measure button on the PowerPivot ribbon.
Create an explicit measure manually in the PowerPivot window, in the Calculation Area, by typing a measure name and formula into the formula area of a cell.
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.
Example: Creating an Explicit Measure that uses a Simple Aggregation
This example 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 Build Formulas for Calculations.
This example demonstrates two ways of creating an explicit measure. First, you will create a measure in the PowerPivot window, in a Calculation Area that shows all measures defined in the model. Second, you will add a PivotTable or Pivot Chart to your PowerPivot workbook, and then use the Measure Settings dialog box to add a measure. The formula of a measure defines a sum, average, or other calculation using the columns and tables in the PowerPivot workbook.
In the PowerPivot window, click the Home tab, and in the Views group, click Calculation Area.
In the FactResellerSales table, click a cell anywhere in the Calculation Area.
In the formula bar at the top of the workbook, enter a formula in this format <measurename>:<formula>:
Projected Sales:=SUM('FactResellerSales'[SalesAmount])*1.06Click OK to accept the formula.
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.
Expand the FactResellerSales table to view the measure you just created. If you already had a PivotTable in the workbook prior to creating the measure, you must click the Refresh button at the top of the PowerPivot Field List to refresh the fields.
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 FactResellerSales 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 Total Quantity.
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.
In the Formula text box, position the cursor after the equal sign (=), and then enter the following formula:
SUM(FactResellerSales[OrderQuantity])Click OK.
Both measures that you created are saved with the source data table, but can be used by any PivotTable or PivotChart. The measures appear in the PowerPivot Field List and are available to all users of the workbook.
Example: Creating an Explicit 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 2005-2008 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 2007. 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 2008.
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 2005-2008.
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 2007 accounted for 31.71% of all sales from 2005-2008.
All Reseller Sales |
Column Labels |
|
|
|
|
Row Labels |
Accessories |
Bikes |
Clothing |
Components |
Grand Total |
2005 |
0.02% |
9.10% |
0.04% |
0.75% |
9.91% |
2006 |
0.11% |
24.71% |
0.60% |
4.48% |
29.90% |
2007 |
0.36% |
31.71% |
1.07% |
6.79% |
39.93% |
2008 |
0.20% |
16.95% |
0.48% |
2.63% |
20.26% |
Grand Total |
0.70% |
82.47% |
2.18% |
14.65% |
100.00% |
See Also
Reference
Key Performance Indicator Dialog Box
Concepts
PowerPivot Window: Calculation Area