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.