Microsoft Office Tutorials and References
In Depth Information
Even if the CalculatedOrderFreightFirstTrial formula works as expected, it is very interesting to
investigate a bit more to try to understand why it has been necessary to define two distinct
measures. To reach your goal, you had to define a measure (CalculatedFreight) that seems
useless because a simple substitution would lead you to this more compact definition:
CalculatedOrderFreightSecondTrial = SUMX( Orders, VALUES( PriceList[Freight] ) )
Nevertheless, if you tried to use this second formula, PowerPivot would raise an error and
would not be able to perform the computation because SUMX creates a row context and
evaluates its second parameter without modifying the filter context. You should remember,
from Chapter 6, that the presence of a row context does not modify the filter context, so the
expression VALUES (PriceList[Freight]) is evaluated in a context wherein it returns multiple
rows, yielding to an error. On the other hand, when you pass the measure CalculatedFreight
as the second parameter to SUMX, you request the evaluation of a measure, so a new filter
context is created for its evaluation, wherein any row context is automatically transformed
into a correspondent filter context.
If a light suddenly turns on in your brain and says CALCULATE, you are one of the most
careful readers we could ever dream of. If not, do not worry, it is absolutely normal—it
took us a really long time to discover (and remember at the right time) that the CALCULATE
function does exactly this: it opens a new filter context that transforms any row context
into a filter context. So a more compact representation of the same formula might be this:
CalculatedOrderFreightThirdTrial = SUMX( Orders, CALCULATE( VALUES( PriceList[Freight] ) ) )
Unfortunately, even this formula does not work as expected; to discover the reason, you need
to study the question a little longer. It turns out that simply adding the measure to a PivotTable
raises the error shown in Figure 10-33.
FIguRE 10-33 Trying to compact a formula sometimes raises unexpected errors.
It seems that, even if CALCULATE opens a correct filter context, the VALUES function still
returns multiple values. But if you try to add the formula after having filtered the couriers
to show only one of them, the formula works correctly, as you can see in Figure 10-34.