Calculating Totals

Total values in data table can be set up to be displayed by rows and/or columns.

Displaying Totals

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

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

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

NOTE. To calculate the mean as 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 totals by rows:

The example of totals by columns:

To set up calculation by edited data without preliminary data saving to the source, Setting Up Formula Calculation is used.

Setting Up Totals

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

Totals Calculation Options

To set up totals calculation options, use the Calculation Options or 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 row, if Mean 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, then use the Output String side panel tab to display the total or total by hierarchy in the parent element string.

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

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 one of the total types applied to the table from the drop-down list.

Select name type from the list:

Inital. Displays initial element name
Total Method. Displays total method name.
Composite. Displays name of the source element and total method.

Determine separator between element and total method names in the input field. Item is available on selecting the Composite name type.

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

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

Select the Total and Total by Hierarchy checkboxes and determine settings of the displayed total: the type - Amount, the element name - Initial. The initial 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 Headers Formatting tabs in the Totals group of tabs of the side panel.

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

Hiding Totals

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

Totals by Rows /

Totals by Columns button on the ribbon tab: