“How many customers spent 800 or more?” has two steps: add up each customer’s sales, then count the
customers above 800. A single countIf cannot do it, because its condition looks at one row at a time.
The total per customer comes from a level-aware calculation.
Copy and paste
1. Total per customer (save as {Customer Total}):
sumOver({Sales}, [{Customer}], PRE_AGG)
2a. A flag to group or colour by:
ifelse({Customer Total} >= 800, 'Above', 'Below')
2b. Or the customer, only when above (save as {Customer Above}):
ifelse({Customer Total} >= 800, {Customer}, NULL)
3. The count: put {Customer} with Count distinct in a visual grouped by the flag, or use
{Customer Above} with Count distinct in a KPI. distinct_count ignores the nulls of the customers
below the line.
A threshold the reader chooses (a Decimal parameter named Threshold):
ifelse({Customer Total} >= ${Threshold}, {Customer}, NULL)
Example
The seven orders of the conditional logic guides give these customer totals: Anna 1,200, Carla 900, Bruno 350, Dario 50. Two customers are at or above 800.
By region, counting customers whose total, across both regions, is at or above 800:
| Region | Customers | Above 800 | Their sales in the region |
|---|---|---|---|
| North | 2 | 1 | 700 |
| South | 3 | 2 | 1,400 |
Anna counts in both regions because her total is computed per customer, not per region. Her North sales
alone, 700, would be below the line. To classify customers by their sales in each region, partition by
both: sumOver({Sales}, [{Customer}, {Region}], PRE_AGG).
A row condition is a different question
distinct_countIf({Customer}, {Sales} >= 800) counts customers with at least one order of 800 or more.
In the example that is Carla only: Anna’s 1,200 is spread over three orders of 300, 500 and 400. Make sure
the report asks the question the formula answers.
Which total
PRE_AGG is computed after the analysis filters, so with a date filter on May the totals, and the count,
are for May. For a lifetime threshold that ignores the reader’s filters, use PRE_FILTER instead; see
PRE_AGG vs PRE_FILTER explained.
A filter on {Customer Total} >= 800 is the third way: it keeps only those customers’ rows on the sheet,
so every visual, not only the count, shows the customers above the line.
Checked against the Amazon Quick Sight documentation (October 2026). The example results were recomputed in DuckDB.
