Aggregate functions

GrandTotal

View as Markdown

The GrandTotal function returns the grand total for all records in a column based on a provided aggregate formula. GrandTotal is shorthand for Subtotal(aggregate, “grand_total”).

Usage notes

  • The GrandTotal function can be used to return a summary aggregation for a column in each row of the column. For example, in a table with a column [Number of Orders] containing the number of orders per customer, you can use the formula GrandTotal(Sum([Number of Orders])) to repeat the total number of orders for all customers in each row. You can contrast this with Sum([Number of Orders]), which returns the number of orders per customer. This can be used to make comparisons between each customer and the overall total.

Syntax

GrandTotal(aggregate)

Function arguments:

  • aggregate (required) - The aggregate formula to use in the function.

  • Examples: Count(), Sum([Sales Amount])

Example

In a chart visualizing order counts per Product Type and Region, you can add a calculated column with the formula to the tooltip:

GrandTotal(CountDistinct([Order Number]))

 To see an overall total count of distinct orders across all categories.

  • The calculation is shown in the tooltip in this example, but can be used anywhere an aggregate formula is allowed.

    The function GrandTotal(CountDistinct([Order Number])) is used in a tooltip for a trellis bar chart. A user can now compare the total for each group to the grand total when hovering