The subtotalling that occurs automatically inside a pivot table may not always be appropriate when grouping by dimension values.
By default Excel will sum the amounts, but if the figures are percentages, this does not really make sense. Excel offers some end user options by right-clicking the measure, but this does not cover all scenarios.
Another option is to change the “AggregateFunction” option attached to the measure inside Visual Studio. Often however the available choices are limited if you do not have a time or account dimension in your cube.
The other way to go about things is to use a cube calculation. The scenario I will use here revolves around a fictional figure “Turnover Percentage”. Here is the initial problem:
There are two possible options: Display no subtotal or display the average of the items. To do either of these, the following must be done:
Create a ‘Record Count’ measure in your cube. The data source should be ‘Turnover Percentage’, but note that the aggregation is set to ‘Count’.
Set the Visible property for both ‘Turnover Percentage’ and ‘Record Count’ to False.
Create a new Calculated Member with the following settings:
The expression above will only show the value if there is a single record. The result in Excel will be the following:
If instead you want to display the average, change the function to be:
iif([Measures].[Record Count] > 1, [Measures].[Turnover Percentage] / [Measures].[Record Count], [Measures].[Turnover Percentage])
The result will then be:
Technical Reflections 