Microsoft Office Tutorials and References
In Depth Information
Now, let us focus on the formula to define a new measure. In a first attempt, you might
try to define it by using the same DAX expression used in the calculated column, but you
would get the error shown in Figure 3-13.
FIguRE 3-13 Error defining GrossMarginPerc measure with the same DAX expression as calculated column.
You display the error message by clicking the Check Formula button. The reason for the error is
that the context of execution is not a single row, but a group of rows. That group corresponds
to the selection that is implicitly defined by the cell that has to be calculated in the pivot table.
For example, in Figure 3-8, each gross margin percentage cell corresponds to the set of rows
that have the same year. In such a context, which contains multiple rows, there is no way to
refer to the value of the column Sales[SalesAmount] because a column has a value only when
it is used in the context of a single row.
To avoid the error, you need to define an expression that divides the sum of the gross margin
by the sum of the sales. This expression makes use of the SUM function, which aggregates
all the rows filtered by the current selection in the pivot table (which will be the year, as we
said before). The following is the correct DAX expression for the measure; it is also shown
in Figure 3-14.
= SUM( Sales[Gross Margin] ) / SUM( Sales[SalesAmount] )