Skip to main content

How to aggregate values on specific groups of data

Another common use for Power Scripts is calculating totals for specific groups of data across all records in a result set. Examples include each major's share of overall enrollment, or the sales total for each salesperson.

To do it, use the reserved $local variable, which keeps its values across multiple records (see Reserved Power Script variables). Because $local is an object, you can create properties on it to store values, and here you create one property for each unique value, or group.

The main steps for calculating a total per group are:

  1. Initialize: use a Power Script Flow Step to create properties on $local that track each group's total as the script runs through the records.
  2. Finalize: use a Flush Flow Step so the first Power Script finishes running for every record in the result set.
  3. Utilize: use a second Power Script Flow Step to read the finished totals.

These examples assume one Field identifies the group (groupFieldAlias), such as major or salesperson, and another holds the number to total for each group (valueFieldAlias).

Step 1: Initialize and increment the group totals​

// Initialize a local object to store totals for each group
$local['groupTotals'] = $local['groupTotals'] || {};

// Extract the group identifier and the value to be totaled from the current record
var group = $record['groupFieldAlias'];
var value = $record['valueFieldAlias'];

// Initialize the total for the group if it doesn't exist
$local['groupTotals'][group] = $local['groupTotals'][group] || 0;

// Add the current value to the group's total
$local['groupTotals'][group] += value;

Here is what each part does.

1. Initialize the $local['groupTotals'] object:

$local['groupTotals'] = $local['groupTotals'] || {};
  • This line creates an object property on $local to hold the running total for each group.
  • The || {} means that if $local['groupTotals'] does not exist yet (it is undefined or null), it starts as an empty object {}. That avoids errors when the script reads the object later.

2. Extract the group identifier and the value to be totaled:

var group = $record['groupFieldAlias'];
var value = $record['valueFieldAlias'];
  • group stores the value of the Field that identifies the current record's group, taken from groupFieldAlias.
  • value stores the number to add to that group's total, taken from valueFieldAlias.
  • groupFieldAlias and valueFieldAlias are placeholders for the Field aliases in your data.

3. Initialize the group total if it doesn't exist:

$local['groupTotals'][group] = $local['groupTotals'][group] || 0;
  • This line checks whether $local['groupTotals'] already has a total for the current group.
  • If it does, that total is kept. If it does not (the total is undefined), the total starts at 0.
  • So every unique group gets its own running total, and later records in the same group keep adding to it.

4. Add the current value to the group's total:

$local['groupTotals'][group] += value;

Each time this line runs, it adds the current record's value to the running total for its group. For example, if group is "A" and value is 10, it adds 10 to group "A"'s total.

Step 2: Finalize the totals with a Flush Flow Step​

After the first Power Script, add a Flush Flow Step. Flush makes every earlier Power Script run against all records of the result set before any later Flow Step starts, so the per-group totals hold the sum from every record.

Step 3: Utilize the final totals​

In a second Power Script placed after the Flush, look up the current record's group and write its total to a new Field:

// Extract the group identifier from the current record
var group = $record['groupFieldAlias'];

// Assign the calculated total for the group to a new field in the record
$record['groupTotal'] = $local['groupTotals'][group];

Every record now carries its group's total in the new groupTotal Field.

To total one Field across every record instead of per group, see How to calculate a sum total aggregate via a Power Script.