.png)
Measures in PowerPivot
A measure is a numeric calculation used in data analysis. Examples that are commonly found in business reports include sums, averages, minimum or maximum values, counts, or more advanced calculations that you create using a DAX formula. In a PivotTable or PivotChart report, a measure is placed in the Values area, where the row and column labels that surround it determine the context of the value. For example, if you are measuring sales by year (on columns) and region (on rows), the value of the measure is calculated based on a given year and region. In a PivotTable or chart, the value of a measure always changes in response to selections on rows, columns, and filters, allowing for ad hoc data exploration.
Note
|
|---|
|
Although measures and calculated columns are similar in that both are based on a formula, they differ in how they are used. Measures are most often used in the Values area of a PivotTable or PivotChart. Calculated columns are used when you want to place calculated results in a different area of a PivotTable (such as a column or row in a PivotTable, or on an axis in PivotChart). For more information about calculated columns, see Calculated Columns. |
Measures are either implicit or explicit, which affects how you use them in a PowerPivot workbook, and in other applications that use PowerPivot data.
- Measure
-
A measure is a formula that is created specifically for numeric data that you want to summarize or analyze in a PivotTable, PivotChart, or Power View report 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.
- Implicit Measure
-
An implicit measure is created by Excel when you drag a field, such as Sales Amount, to the Values area of the PowerPivot Field List. Because implicit measures are generated by Excel, you might not be aware that a new measure has been created. But if you examine the Values list closely, you will see that the Sales Amount field is actually a measure named Sum of Sales Amount and appears with that name in both the Values area of the PowerPivot Field List, and on the PivotTable itself.
Implicit measures can only use a standard aggregation (SUM, COUNT, MIN, MAX, DISTINCTCOUNT, or AVG), and must use the data format that is defined for that aggregation. In addition, implicit measures can only be used by the PivotTable or chart for which they were created. You cannot use them in a Power View report
An implicit measure is tightly coupled with the field upon which is it based, affecting how you delete or modify the measure later. For more information, see Edit or Rename a Measure in a PivotTable or PivotChart and Delete a Measure in a PivotTable or PivotChart.
- Explicit Measure
-
An explicit measure is created by you when you type or select a formula in a cell in the Calculation Area, or when you click New Measure in the PowerPivot ribbon.
Explicit measures can be used by any PivotTable or chart in the workbook and by Power View reports. Moreover, they can be extended to become a KPI, or formatted using one of the many formatting strings available for numeric data. Context menu commands for Create KPI and Format are only available when you are using an explicit measure.
Note
Once you use a measure as a KPI, you cannot use it for other calculations; you must make a copy if you want to use the formula in calculations as well. For more information about KPIs, see Key Performance Indicator Dialog Box.
The sales manager at Adventure Works has been asked to provide reseller sales projections over the next fiscal year. She decides to base her estimates on last year’s sales amounts, with a six percent annual increase resulting from various promotions that are scheduled over the next six months.
To develop the estimates, she imports last year’s reseller sales data and adds a PivotTable. She finds the Sales Amount field in the Reseller Sales table and drags it to the Values area of the PowerPivot Field List. The field appears on the PivotTable as single value that is the sum of all reseller sales from last year. She notices that even though she did not specify the calculation herself, a calculation has been provided automatically, and the field has been renamed to Sum of Sales Amount in the field list and on the PivotTable. A built-in aggregation added by Excel, =SUM('FactResellerSales'[SalesAmount]), provides the calculation. She renames the implicit measure Last Year Sales.
The next calculation is sales projection for the coming year, which will be based on last year’s sales multiplied by 1.06 to account for the expected 6 percent increase in reseller business. For this calculation, she must create the measure explicitly, using the New Measure button to create a calculation named Projected Sales. She fills in the following formula: =SUM('FactResellerSales'[SalesAmount])*1.06. The new measure is added to Values area in the PowerPivot Field List. It is also added to the table that is currently active in the PowerPivot Field List. The table provides a location for the measure in the workbook. Because she prefers to have the measure in a different table, she edits the measure to change its table association.
Very quickly and with minimal effort on her part, the sales manager has the basic information in place. She can now further assess her projections by filtering on specific resellers or by adding product line information to verify that the future promotions are for products that the reseller carries.
The following topics contain additional information about working with measures.
Create a Measure in a PivotTable or PivotChart
