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.
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.