Microsoft Office Tutorials and References
In Depth Information
You obtain the QtyAllChannels column in the PivotTable that always returns the sum of all the
Quantity data from the Orders table, without considering the filter on the Channel attribute,
as you can see in Figure 6-30.
FIguRE 6-30 Total Quantity for all the channels.
However, if you add the Color attribute to the rows of the PivotTable, the QtyAllChannels is
filtered by that attribute, too. In fact, for each color, you have two rows for a Channel and
four rows that take into account all the channels. In Figure 6-31, you can see that the sum of
Quantity for row 5 of the PivotTable (<Internet, Green>) corresponds to 192, which is the sum
of Quantity for the rows that belong to the intersection between the Internet and the Green
filters. Meanwhile, the QtyAllChannels calculation is affected by only the Green filter and
does not consider the Channel filter on Internet; for this reason, the resulting value of 204 is
calculated by summing the Quantity of all four rows that are Green.
FIguRE 6-31 The effects of filtering on Channel and Color.
At this point, you might wonder whether the CALCULATE filter parameters are enlarging or
restricting the context of evaluation. The rule is the following one.