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.
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.
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.