Shape · Summarize and reshape

Group Aggregate

Works out a group total and writes it onto every row instead of collapsing them, as the value, a ratio or a percent. For example, each sale as a share of its region's total.

Also known as: Percent of total, window total

How it works

Pick the Group by columns, or none for a grand total over all rows, the Column to total and a Function: Sum, Count, Average, Min, Max, Median or Std dev. Mode decides what is written: Value puts the group total on each row, Ratio (share of group) divides the row's value by the total to give a 0 to 1 fraction, % of group gives that times 100. A group whose total is 0 gives null in the ratio and percent modes. The row count never changes. Output column defaults to SUM_amount, amount_ratio or amount_pct depending on the mode. To collapse rows instead, use Aggregate.

Inputs and outputs

Settings

{
  groupBy?: string[]
  column: string
  func: "SUM"|"COUNT"|"AVG"|"MIN"|"MAX"|"MEDIAN"|"STDDEV"
  mode: "value"|"ratio"|"pct" ("ratio" = column / group total as a 0-1 fraction — the one to format as a percent; "pct" = 100 × that ratio, 0-100 percent points)
  outputColumn?: string
}

Example

Each sale should show its share of its region's revenue. Group by region, pick amount, Sum and Ratio (share of group), then format amount_ratio as a percent in Format Table.

More in Summarize and reshape

• Aggregate — Collapses rows into one row per group and works out values for each: sum, count, average, median, mode, percentiles, longest text and more, cash-flow values such as NPV or IRR, and for a geometry the dissolved shape, extent or centroid. With no group, you get one grand-total row.
• Running Aggregations — Adds running totals that build up row by row: sum, average, median, min, max, count or standard deviation, optionally restarting per group. For example, year-to-date sales.
• Pivot — Turns the values of one column into new columns, such as one row per store with a column per month. The reverse of Unpivot.
• Unpivot — Turns chosen columns into rows of name and value pairs. For example, Jan, Feb and Mar columns become month and sales rows.
• Transpose — Swaps rows and columns, so every column becomes a row. Best for small, wide summary tables.
• Split to Rows — Turns a cell holding several delimited values into one row per value. For example, 'red;green;blue' becomes three rows.
• Split to Columns — Breaks one text column into several columns at a delimiter, such as 'London, UK' into city and country.
• Explode — Turns each item of a list column into its own row. For comma-separated text, use Split to Rows instead.