Using a Custom Total Calculation

 

Contents[Hide]

1. Overview

In some cases, it is useful to apply a custom total formula to a measure. For instance, when calculating percentage that a measure value is of the total of several measures, the sum or the average of the calculated value does not correctly represent the overall percentage difference when aggregating the value.

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 calculations allow a custom total calculation to be performed.

2. Creating a custom total function

The custom total function is located in the measure configuration panel in the Metric Set Default Values section in the Measure configuration panel.  This example will add the formula A /(A+B) as the total calculation.

Table with data and incorrect totals
Table with data and incorrect totals

To create a custom calculation rule click on “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 the same manner as for a formula measure. Formulas and measure placeholders can be used. When creating a calculation, the measure placeholders represent a set of values used in the total, rather than calculated aggregated values. In the example. $A$ represents the set of values {1.0,2.0,3.0,5.0,7.0,11.0} not the single value 23.0.

In order to use the calculated values, the formula must contain the same aggregations. In the example below the measures A and B both aggregate using SUM. Therefore the formula (A/(A+B)) must contain the same aggregations.

Using a custom total formula
Using a custom total formula

Once you complete the formula click apply, if it goes red, there is an error. Otherwise click close and OK.

Enabling Custom total calculation
Enabling Custom total calculation

3. Other Options

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

Custom total options
Custom total options

Grand Totals Only – only applies the formula to Grand totals, not both Subtotals and Grand Totals.

Calculation Axis – Use the custom calculation on the Column total, or the Row total.

Use Detail Levels – If selected, Grand totals use the set of data created from the detail level, i.e. the most detailed level of the data. If unselected, Grand totals are calculated using the set of values created from the Sub Totals.

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

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