Calculated field
{ EXCLUDE [Team] : SUM([Revenue]) }
Sample data
| Region | Team | Customer | Revenue | Seats | Date |
|---|---|---|---|---|---|
| North | Cedar | Aster | 120 | 4 | 2026-01-05 |
| North | Cedar | Aster | 80 | 2 | 2026-02-05 |
| North | Cedar | Birch | 300 | 6 | 2026-01-12 |
| North | Pine | Clover | 90 | 3 | 2026-02-14 |
| South | Elm | Dahlia | 200 | 5 | 2026-01-08 |
| South | Elm | Dahlia | 100 | 2 | 2026-02-08 |
| South | Ash | Fern | 75 | 3 | 2026-01-20 |
| South | Ash | Fern | 25 | 1 | 2026-02-20 |
The same calculation as SQL
SELECT Region, Team, MAX(total) FROM (SELECT * FROM input WHERE Region='North') JOIN (SELECT Region, SUM(Revenue) total FROM (SELECT * FROM input WHERE Region='North') GROUP BY Region) USING(Region) GROUP BY Region, Team ORDER BY Region, Team
Runs the example above.
SELECT Region, Team, MAX(total) FROM (SELECT * FROM input WHERE Region='North') JOIN (SELECT Region, SUM(Revenue) total FROM (SELECT * FROM input WHERE Region='North') GROUP BY Region) USING(Region) GROUP BY Region, Team ORDER BY Region, Team
Written for this dialect; not run here.
SELECT Region, Team, MAX(total) FROM (SELECT * FROM input WHERE Region='North') JOIN (SELECT Region, SUM(Revenue) total FROM (SELECT * FROM input WHERE Region='North') GROUP BY Region) USING(Region) GROUP BY Region, Team ORDER BY Region, Team
Written for this dialect; not run here.
Result
| Region | Team | result |
|---|---|---|
| North | Cedar | 590 |
| North | Pine | 590 |
Same rows from the calculation and from the SQL in DuckDB.
Where a naive translation goes wrong
- Computing EXCLUDE on the unfiltered input includes marks removed by the dimension filter.
Naive SQL
SELECT Region, Team, MAX(total) FROM input JOIN (SELECT Region, SUM(Revenue) total FROM input GROUP BY Region) USING(Region) GROUP BY Region, Team ORDER BY Region, Team
Region Team result North Cedar 590 North Pine 590 South Ash 400 South Elm 400 Matches the calculation
SELECT Region, Team, MAX(total) FROM (SELECT * FROM input WHERE Region='North') JOIN (SELECT Region, SUM(Revenue) total FROM (SELECT * FROM input WHERE Region='North') GROUP BY Region) USING(Region) GROUP BY Region, Team ORDER BY Region, Team
Region Team result North Cedar 590 North Pine 590
Signature
{ EXCLUDE [Team] : SUM([Revenue]) }