|Table of Contents|
Summaries create aggregated totals in order to provide the user with higher level data as part of a more detailed report. There are several types of summary that can be included in a table:
- Field Totals
- Categorical Sub Totals
- Section Totals
- Section Summary Tables
- Grand Totals
Depending on the type of summary you are creating, you will have access to a selection of the following types:
|Sum||This will add all of the values in the field to create the total.|
|Average||This will find the mean value of all the values in the field to create the total.|
|Count||This will count the number of values in the field to create the total.|
|Count Distinct||This will count the number of unique values in the field to create the total.|
This is only available if the field is a calculation, and uses the calculation to create the total.
Note: To display the total of a calculated field, ensure that each individual field included in the calculation formula:
Standard Table Summaries
There are two methods for adding a total to a field in a standard Column or Row based table:
Field Drop Down
Column Formatting menu
Sub Totals are used when there are multiple records per category, possibly caused by having multiple dimension fields in a table, and it would be beneficial to view a total value for each category value. The example shown here could use a sub total for Year, as there are multiple rows per year value due to the Quarter field.
When using Sub Totals, we often pair this functionality with the Suppress Duplicates option, to make the report output easier to consume. To apply this option:
Totals and subtotals can be moved to the start of a table or section too, so totals might appear in the top row and left column and subtotals might appear at the top row or column of their section within a table. In the Column Formatting dialog box, look for the Move Total Value Location toggle (and the Move Sub Total Value Location toggle, further down) and activate them to see totals at thes tart of a table and subtotals at the start of a section.
Section Total & Labels
Cross Tab Summaries
Sub-totals work on multiple levels in cross-tabs reports. You can calculate row totals across columns, and column totals across rows. You can even get totals of column totals and row totals. This is also done at the grand total level; that is, a total of two separate sections can be done across rows and columns. Refer to the example screenshots.
Row sub-totals on columns
Column sub-totals on rows