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.