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.