Measure values in a dimension can be summarized using aggregate functions to obtain summary values for different levels in a hierarchy. For example, in the Fiscal dimension, measure values that apply over a month can be summarized at the quarter level and again at the fiscal year level. In this way, a dimension hierarchy in a planning worksheet can be expanded or collapsed to expose more detailed or summarized data.
Measure values can be summarized differently in each dimension. Measure values cannot be summarized in the measure dimension.
The following aggregate functions can be used to summarize measure values in a dimension, depending on the type of dimension.
Count returns a count of the values in the next lowest level of a dimension hierarchy.
For example, in the Fiscal dimension, the dimension member Q2 '05 has three children in the hierarchy: April, May, and June. The summary value for Q2 '05 is therefore three. This function is used in dense and time dimensions only.
Max returns the highest value in the next level down in a dimension hierarchy; that is, the maximum value of the children in the hierarchy.
For example, in the Fiscal dimension, the dimension member Q2 '05 has three children in the hierarchy: April, May, and June. If values for April, May, and June are 14, 11, and 15 respectively, the summary value for Q2 '05 is 15. This aggregate function is used only in dense and time dimensions.
Min returns the lowest value in the next level down in a dimension hierarchy; that is, the minimum value of the children in the hierarchy.
For example, in the Fiscal dimension, the dimension member Q2 '05 has three children in the hierarchy: April, May, and June. If values for April, May, and June are 14, 11, and 15 respectively, the summary value for Q2 '05 is 11. This aggregate function is used only in dense and time dimensions.
Sum returns the sum of the values in the next level down in a dimension hierarchy; that is, the sum of the values of the children in the hierarchy.
For example, in the Fiscal dimension, the dimension member Q2 '05 has three children in the hierarchy: April, May, and June. If values for April, May, and June are 4, 1, and 5 respectively, the summary value for Q2 '05 is 10. This aggregate function summarizes values in sparse, dense, and time dimensions.
Avg returns the average of the values in the next level down in a dimension hierarchy; that is, the average value of the children in the hierarchy.
For example, in the Fiscal dimension, the dimension member Q2 '05 has three children in the hierarchy: April, May, and June. If values for April, May, and June are 4, 3, and 5 respectively, the summary value for Q2 '05 is 4. This function is used in dense and time dimensions only.
Last_in_Period—Used only for time dimensions (Fiscal and Manufacturing). Returns the value for the last member in a sequence of children.
For example, in the Fiscal dimension, the dimension member Q2 '05 has three children in the hierarchy arranged in the sequence April, May, June, with values 4, 3, and 5 respectively. The summary value for Q2 '05 is the value for June (5). This function is used in dense and time dimensions only.
First_in_Period—Used only for time dimensions (Fiscal and Manufacturing). Returns the value for the first member in a sequence of children.
For example, in the Fiscal dimension, the dimension member Q2 '05 has three children in the hierarchy arranged in the sequence April, May, June, with values 4, 3, and 5 respectively. The summary value for Q2 '05 is the value for April (4). This function is used in dense and time dimensions only.
In dense and time dimensions, measures can be summarized using any of the previously described aggregate functions. In sparse dimensions, measures can be summarized only using the sum aggregate function.
See Understanding Cubes, Dimensions, and Measures Data Types.