Microsoft Office Tutorials and References

In Depth Information

Closing Balance over Time

In a PivotTable, each cell contains the result of applying an aggregation function to a measure.

Whenever that function is SUM, the measure is called an additive measure because SUM is

applied over all dimensions. Whenever another function is applied, such as AVERAGE, MIN,

or MAX, the measure is called a nonadditive measure because an aggregation function

other than SUM is applied over all dimensions. However, it is important to note that both

for additive and nonadditive measures, the same aggregation function is always applied

over all dimensions, without exception.

Semiadditive Measures

Some measures should behave in a different way. For example, think about the balance for

a bank account. If you consider several accounts, you can calculate the total balance for an

occupation by summing up all the balances of customers grouped by occupation. However,

you cannot sum the same balance twice, and you probably have several balances of the same

account that measure it over time. For example, in Figure 7-49, you can see a Balance table

in Excel: the same account has a balance value for each date. This type of measure is called

a semiadditive measure, because it can be aggregated using SUM over some dimensions but

requires a different aggregation algorithm over other dimensions. You can find the following

example in the CH07-11-SemiAdditive.xlsx workbook included on the companion DVD.

FIguRE 7-49
The raw balance account data.

In the case of account balance data, the only dimension that cannot be summed is the Date.

With the term
dimension Date,
we include all the attributes of a Dates table related to the table

containing the real measures. The logic that has to be implemented for the Date attributes is to

consider only the values belonging to the last date in the evaluated period. In other words, you

must implement a logic that can produce the same results that you see in Figure 7-50.