Microsoft Office Tutorials and References
In Depth Information
It is not fruitful to describe all the different options in this area; the interested reader can
easily try them and look at the different results that can be obtained with different kinds
of calculations. Instead, we want to note a couple of things:
■ Because the calculation is carried on by Excel after the PivotTable has returned the
results, this feature works with any type of PivotTable.
■ This is an Excel setting, which means that the measures can be shown in the PivotTable,
but you cannot use this technique to perform any kind of further computation based
on these values.
This last point might be an issue if you ever need to compute such a ratio and then use it
further. Excel does not help you get past this potential shortcoming. You definitely need to
exploit DAX power. You learn how to make these calculations in both calculated columns
and measure in Chapter 9, “PowerPivot DAX Patterns.”
Aggregating Data Without using Sum
In most of the examples that you have seen up to now, you used the SUM function to
summarize data over attributes. For example, if you think about the various date attributes, the
aggregate of sales amounts over a year or over a month is the sum of all the sales amounts
in that period. The same aggregation applies to any other attribute in any other table. It
does not matter whether you want to aggregate by the product color or the sales territory,
the aggregate value has always been the sum.
Nevertheless, there are some situations in which you should not sum up all the values to get
the total. Instead, you need different aggregators. You saw some examples of this when you
learned how to handle the distinct count measure. Distinct count does not aggregate by sum;
it uses a special aggregation instead. So it needs to be computed as a measure and cannot
be implemented as a calculated column.
The distinct count example clearly shows that the aggregation function depends on the
measure—that is, different measures might need different aggregators. These measures
are called nonadditive, and they require aggregators that are not the simple sum.
Moreover, there are frequent cases in which the aggregation function depends not only on
the measure, but on the dimension used to slice data too. When we face such a situation, we
speak about semiadditive measures . Usually, semiadditive measures are related to calculation
over time, and you can read how to handle these scenarios in the last part of Chapter 7, “Date
Calculations in DAX.”
PowerPivot, by default, handles only additive measures. It uses the SUM aggregation on
all numeric columns and COUNT on all strings of all tables. Because PowerPivot does not
distinguish additive from nonadditive measures, it needs a default behavior.