Using a custom total calculation


1. Overview

In some cases, it is useful to customize the total displayed for a measure. For instance, when calculating the percentage that a measure value makes up of the total of several measures, the sum or the average of all the calculated values in the rows may not be as helpful as the calculating the percentage again based on the measures' overall totals.

Group A B A / (A+ B)    
Apple 1 7 12.50%    
Apple 2 11 15.38%    
Apple 3 13 18.75%    
Apple 5 17 22.73%    
Apple 7 19 26.92%    
Apple 11 23 32.35%    
Total: 29 90 X SUM 128.64%
Banana 13 29 30.95% AVG 21.44%
Banana 17 31 35.42% Calc. 24.37%
Banana 19 37 33.93%    
Banana 23 39 37.10%    
Banana 29 41 41.43%    
Banana 31 43 41.89%    
Total: 132 220 X SUM 220.71%
        AVG 36.79%
        Calc. 37.50%

Totals calculation rules allow you to customize how the totals are calculated.

2. Creating a custom total formula

This example will add the formula A /(A+B) as the total calculation.

Table with data and totals calculated using an aggregator
Table with data and totals calculated using an aggregator

To create a totals calculation rule, click on the measure in the data analysis panel to open its measure configuration dialog, then click Set up totals calculation rule formula.

Enabling custom total calculation
Enabling custom total calculation

The formula bar will appear.

Custom total formula bar
Custom total formula bar

Formulas are entered in a similar way as for a regular formula measure: formula functions and measure placeholders can be used.

When referring to a measure, a function (e.g., AVG or SUM) must be used to determine how its set of values is aggregated for your customized total. In the example above, the measures A and B both aggregate using Sum, and we will want to use the corresponding SUM function for each measure in order to calculate the overall percentage in this case. You are free to choose functions that don't correspond with the measure's aggregator if needed for your purposes.

Using a custom total formula
Using a custom total formula

Once you complete the formula, click Apply and then close to return to the measure configuration dialog. You can then submit the dialog when finished.

Enabling Custom total calculation
Enabling Custom total calculation

3. Other options

There are several options that can influence how the custom total formula is used.

Custom total options
Custom total options

Grand Totals Only – only applies the formula to the grand total at the beginning or end, and not the subtotals displayed when there are multiple hierarchies selected under ROWS or COLUMNS in the metric set.

Calculation Axis – determines if the custom calculation applies to the column totals or the row totals.

Use Detail Levels – If checked, grand totals use the set of data created from the details level, i.e., the lowest-selected level of the last hierarchy selected. If unchecked, grand totals are calculated using the set of values created from the subtotals.

Aggregator – The aggregation that is applied to the axis opposite that of the totals formula. If Calculation Axis is Columns, this is applied to Rows. When Grand Totals Only is selected, it is also applied to subtotals of the Calculation Axis.

4. See also

Dundas Data Visualization, Inc.
500-250 Ferrand Drive
Toronto, ON, Canada
M3C 3G8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours: 7am-6pm, ET, Mon-Fri