# Using a Custom Total Calculation

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

To create a custom calculation rule click on “Set up Totals Calculation Rule Formula”.

The formula bar will appear.

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.

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

## 3. Other Options

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

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.