Microsoft Office Tutorials and References
In Depth Information
FIguRE 3-14 Correct definition of the GrossMarginPerc measure.
Finally, you can check that the measure works correctly by comparing the data, as shown in
FIguRE 3-15 GrossMarginPerc, correctly computed, returns the same value as the G column calculated in Excel.
It is worth noting that if you use the same DAX expression you see in Figure 3-14 in a calculated
column, it would calculate the gross margin of all the rows in the table, duplicating the same
number for each row. In fact, in a calculated column, the SUM function gets all the rows of the
current table, without being filtered by the user selection on the pivot table.
Differences Between Calculated Columns and Measures
Even if they look similar, there is a big difference between calculated columns and
measures. The value of a calculated column is calculated during data refresh and uses the
current row as a context; it does not depend on user activity on the pivot table. A
measure operates on aggregations of data defined by the context of the current cell: source
tables are filtered according to the coordinates of the cell, and data is aggregated and
calculated using this filter. In other words, a measure always operates on aggregations
of data under the evaluation context and for this reason there is no way to reference a
single row in a DAX expression. The evaluation context is explained further in Chapter 6.