# Calculate per group with FOREACH

> Work out a total per customer, per product or per anything, then group, color or filter your chart by it.

Prepare · 4 min read · Updated 2026-10-01

Some questions need two levels at once. "How many of my customers spent over $1,000?" means adding up sales for each customer first, then sorting customers into groups. Your data is usually one row per order, so the per-customer total doesn't exist yet. `FOREACH` creates it.

```text
FOREACH([Customer], SUM([Sales]))
```

Read it as "for each customer, sum sales." Every row gets its customer's total sales, so an order from a customer who spent $4,200 overall carries 4,200. Because the value sits on every row, you can group by it, color by it, or filter on it like any other field.

## Write a FOREACH field

1. Open the Create Calculated Field dialog (see Create a calculated field).
2. Give the field a name, for example **Customer Tier**.
3. Enter the formula. FOREACH is listed with the other aggregates in the formula editor.
4. Save the field. It appears in your field list, ready to drag onto a chart.

FOREACH takes two parts, in this order:

- **The group**: one field, in brackets. This is the "per what": `[Customer]`, `[Product]`, `[Region]`.
- **The calculation**: one aggregate over one field. `SUM`, `AVG`, `COUNT`, `COUNTD`, `MIN`, `MAX` and `MEDIAN` all work.

## Example: tier your customers

Say you run sales ops for a hardware distributor and want to know how many accounts spent more than $1,000. Wrap FOREACH in an `IF` to turn the total into a label:

```text
IF(FOREACH([Customer], SUM([Sales])) > 1000, "High", "Other")
```

1. Save it as **Customer Tier**.
2. Drag Customer Tier onto the X axis. You get two bars, High and Other.
3. Put **Count Distinct** of Customer on the Y axis. Each bar now shows how many customers are in that tier.

> **Tip:** Extra math goes outside the FOREACH. `FOREACH([Customer], SUM([Sales])) * 1.1` works. `FOREACH([Customer], SUM([Sales]) * 1.1)` doesn't, because the inside has to be a single aggregate.

## Count customers, not rows

> **Warning:** A tier labels customers, but the rows underneath are still orders. If you count rows, you're counting orders that belong to High customers. Four customers can easily sit behind seven orders, and both numbers look reasonable. Use **Count Distinct** of the field you grouped by. If your chart counts rows under a FOREACH field, Golden shows a "Counting rows, not Customer" note pointing you to Count Distinct.

Counting rows is still a valid question ("how many orders came from High customers?"), so Golden only points it out. Your chart stays as you built it.

## When you can skip FOREACH

You don't always have to name the group. If you write `IF(SUM([Sales]) > 1000, "High", "Other")` and put it on Color, a label, a tooltip or a table, it already works: the chart has grouped your data, so the total is worked out for each bar or row.

When you put that same field on an axis, Golden needs to know the group before it can draw anything. If your chart makes it obvious, with exactly one other grouping field on the chart or a Count Distinct of one field, Golden uses that group for you.

> **Note:** If there's more than one possible group, Golden won't guess. A wrong group gives you a chart that looks right and answers a different question. Write the FOREACH yourself so the group is spelled out in the formula.

If you put a text-returning field like this on a number axis, the chart explains that there's no height to draw. When the chart has one dimension, it also shows the FOREACH version of your formula to copy into the field. Golden never changes the field for you, because other charts may use it.

## What doesn't work yet

- **One group field at a time.** FOREACH takes a single field. Grouping by Region and Customer together isn't supported.
- **Joined tables.** On a dataset that joins several tables, you can show a FOREACH field but you can't use it as the axis.
- **COUNTD on Amazon Redshift.** Redshift can't count distinct values per group this way. Use `COUNT` inside the FOREACH, or add the distinct count to your chart as its own measure.
- **FOREACH inside another total.** A formula like `COUNTD(IF(FOREACH(…) > 1000, …))` is refused. Put the FOREACH field on the chart instead and use Count Distinct of the field it groups by.

### Related

- [Create a calculated field](https://goldenanalytics.com/docs/calculated-fields)
- [Building charts with shelves](https://goldenanalytics.com/docs/chart-builder)

Source: https://goldenanalytics.com/docs/foreach