Microsoft Office Tutorials and References

In Depth Information

FIguRE 10-34
Imposing a filter on the PivotTable suddenly makes the formula work.

So it seems that the formula is working fine, but only under some circumstances. It turns

out that, when you evaluate the grand total of the PivotTable, the courier is not filtered in

any way. The evaluation context, even under the CALCULATE call, contains more than one

row, creating the multiple rows returned by the VALUES function. If you add some logic to

check whether the VALUES call returns only one value, you can slightly change the formula

definition and provide the final, correct formula:

CalculatedOrderFreight = SUMX( Orders,

CALCULATE( IF( COUNT( PriceList[Freight] ) = 1,

VALUES( PriceList[Freight] ),

BLANK() ) ) )

The IF call checks to see whether the VALUES call returns only one row and, if this is not what

happens, it returns BLANK, avoiding the error that happens for the grand total.

If all the descriptions up to now seem too complex, well, you are right, they are! Nevertheless, it

might be clarifying to show a report (see Figure 10-35) produced by means of this data model

and explain in great detail what happens under the covers to compute it.

FIguRE 10-35
Sample report built with the many-to-many model.

Notice that this PivotTable does not have the grand total for the columns because it is blanked

by the inner IF function. To better understand how this formula works, let us try to follow the

whole path of computation for a single cell.