Totals

As well as dimension values the grid can also show Total values. Total value is an aggregated measure value over a group of dimension values. A Total value is calculated using the default aggregate function for the measure.

The various levels of Total are shown in this partial grid:

Use the dimension context menu to change the position of Totals (before or after), to hide Totals and to manage the aggregate functions.

If in the example above we turn off the Total for the "Seller" dimension and also the Grand Total, set the Total position for "Category" dimension to "Before" and add an aggregate "Count" to it, we achieve this final grid:

The context menu item "From measure" sets Totals to show a value defined on the "Total position" parameter for the measure. This item only has a value for the axis which contains the "Measures" field and is ignored for other axes.

A measure's Total parameters can be modified in the measure editor:

A Total value is calculated using the default aggregate function for the measure. The aggregate function to be used can be changed or can even be implemented using a script, if required.

The Total calculation can be made either on the source dataset values or on total values of a child level. This can be set using the "Calc totals on to totals" checkbox. If this option is checked then the axis from which to take the Total value (either columns or rows) must be set. This can be set using "Use column totals as base".

Sometimes a situation can arise where, according to the dimensions parameters, each axis requires a different aggregate function with a different values for particular cells. So there is a conflict of which aggregate function to use. The "Totals conflict resolution" group-box has options for solving the conflict. A conflicting cell will not be calculated by default.

The "Total position" parameter defines how to show the Total for the axis with the "Measures" field for those dimensions which have the Totals position set to "From measure".

The example below shows the dimension "Manager" Totals set to "From measure", the "Amount" measure Totals set to "Before", the "Cost" measure Totals set to "Hide" and the other measures Totals set to "After". It can be seen that the "Cost" Total is not shown for the "Manager" dimension:

The next example shows the outcome when Total aggregate functions are changed and Total values are calculated on child totals. All three measures are built using the main aggregate function "Sum" by field "Amount". The Total for measure "Amount" is not customized. For measures "Amount1" and "Amount2" the Totals aggregate function is changed to "Average" and the measure "Amount2" uses the "Calculate totals on totals" option:

Grand totals can only be managed through the context menu: