Calculating Totals

NOTE. In the Dashboards tool select the Table > Add Subtotal/Table > Add Total item in the context menu.

Depending on the selected type of total (subtotal, total or totals by rows or columns), the following total calculation methods are available:

Sum.
Average. Average will be calculated by cells with data. If cells are empty, on calculating the average, those cells are not taken into account. For example, if in 18 cells from 20 contain data, the average will be calculated by 18 cells.

NOTE. To calculate arithmetical mean, select the Treat Empty Values as Zeros checkbox.

Minimum.
Maximum.
Number of non-empty.
Number of empty.
Mode.
Median.
Variance.
Root-mean-square deviation.
Skewness.
Kurtosis.
Range.
Variation coefficient.
Sum of squares.
First-order autocorrelation.
Upper decile.
Upper quartile.
Lower quartile.
Lower decile.
Grand total. Grand total based on server aggregation of facts.
Totals from source. It displays aggregated source data. It is available if a data source has configured and enabled aggregation by dimension levels.

The example of row totals:

The example of column totals:

Setting Up Totals

To set up totals, use the Calculation Options, the Totals and the Output String side panel tabs.

Totals Calculation Options

To set up totals calculation options, use the Calculation Options or the Totals tabs on the side panel.

The first method:

Select one or several cells with totals values in the table.
Go to the Calculation Options tab in the Totals group of tabs on the side panel.

The second method:

Select any cell or data range in the table.
Go to the Totals tab in the Table group of tabs of the side panel.

If the Treat Empty Values as Zeros checkbox is selected, empty values in cells are treated as zeros on calculating totals. This is important, for example, on calculating average values.

NOTE. The checkbox selection does not impact the Number of Empty and Number of Non-Empty total calculation and in the report status bar if the Average option is calculated.

The example of deselected and selected checkbox:

If the Ignore Values in Nodes checkbox is selected, values of hierarchy parent elements are ignored on calculation.

NOTE. It is recommended to use the parameter only if data was aggregated into parent element from all child elements.

The example of deselected and selected checkbox:

The Grand Total checkbox determines displaying of grand totals calculated by all table rows or columns.

The By Hierarchy and By Levels checkboxes are relevant if elements of hierarchical dimension of different levels of hierarchy are displayed in the table. Selecting these checkboxes enables the user to display names of parent elements of hierarchy or level names, respectively, within the name of the total element.

TIP. If grand total matches subtotal by hierarchy or by levels, enable grand total to avoid duplicate information.

The example with selected checkboxes: Grand Total + By Hierarchy, Grand Total + by Levels:

If the Output Before Data checkbox is selected, totals are displayed before data cells.

Example:

If the Calculate Total for One Element checkbox is selected, when calculating totals by columns and by rows, the totals are also calculated:

For elements by each level.
For elements with one row or column.

The example of deselected checkbox:

The example of selected checkbox:

Output String

If the table displays elements of hierarchical dimension of various hierarchy levels, use the Output String side panel tab to display total or hierarchy total in the parent element string.

Select one or several cells with totals in the table.
Go to the Output String tab in the Totals group of tabs of the side panel.

To set up display parameters of totals in the parent element string, select one or both checkboxes. The setting is separate for row and column totals.

Select the total type applied to the table in the drop-down list.

Select name type in the list:

Source. It displays source element name.
Total Method. It displays total method name.
Composite. It displays name of the source element and total method.

Determine separator between element and total method names in the edit box. The item is available when the Composite name type is selected.

Select the Grand Total checkbox and determine settings of the displayed total: the type - Sum, the element name - Composite, separator - ",". The source table is shown on the left, the table after the checkbox is selected is on the right:

Select the Hierarchy Total checkbox and determine settings of the displayed total: the type - Sum, the element name - Total Method. The source table is shown on the left, the table after the checkbox is selected is on the right:

Select the Grand Total and the Hierarchy Total checkboxes and determine settings of the displayed total: the type - Sum, the element name - Source. The source table is shown on the left, the table after the checkbox is selected is on the right:

Formatting of Data and Totals Headers

Totals data and headers can be formatted on the Data Formatting and Header Formatting tabs in the Totals group of tabs of the side panel.

Select the cell with totals in the table.
Select the Totals button on the side panel.
Go to the Data Formatting or the Header Formatting tab.

Hiding Totals

Select the Reset item in the drop-down menu of the

Row Totals /