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.

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.