(v23) Using a Custom Total Calculation

Using a Custom Total Calculation

This applies to: Managed Dashboards, Managed Reports

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%
AVG36.79%
Calc.37.50%

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

Creating a custom total formula

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

To create a totals calculation rule, click on the measure in the Data Analysis Panel to open its measure configuration dialog, click to expand Totals Calculation Rules for either rows or columns, then click Define totals calculation rule formula.The formula bar will appear.Formulas are entered in a similar way as for a regular formula measure: you can use formula functions such as SUM, and measure placeholders such as $A$ for our measure A.

Note: You can click a measure in your visualization to insert its placeholder into the formula bar.

There are multiple ways you can determine your totals:

• You can pass measures as arguments to functions such as AVG or SUM to determine how the individual values should be totaled. 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.

• Or, you can instead refer to measures directly without calling a function, to use their original totals as determined by their aggregator. When you do this, you have the option of customizing the aggregator used just for this total by clicking on the underlined measure placeholder to open the Placeholder dialog, then setting Aggregator.

Note: Use measure placeholders without calling functions or customizing the aggregator for optimizations such as avoiding the retrieving of collapsed data from the data source.

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

Other options

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

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.

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.