Lesson 11 of 15 · Lab

Calculated columns vs aggregates explained

A calculated column applies a formula to each row and keeps the row count, like a formula filled down a column in Excel. An aggregate such as SUM or AVG reduces many rows to one value. Calculate at row level first, then aggregate.

Quantity 2 at price 60 and quantity 4 at price 20 give line totals of 120 and 80, still two rows. Their sum, 200, is an aggregate.

In this lab, fictional Vandelay Coffee Supply's 13 invoices have their customers, and North leads by €110 at list value. Starhopper, a North customer, gets a 10% rebate, and nobody else has one.

Three of the 13 invoices, before any rebate
customerregionamount_eurrebate_pct
StarhopperNorth1,24010
WonkaSouth8600
Dunder MifflinNorth6400
Starhopper's invoices come to €4,320 at list. After the rebate, who leads?

South. Ten percent of Starhopper's list value is €432, far more than North's €110 lead.

Net a rebate out of list prices

  1. Formula is selected in this live flow in Strata, Oplema's browser app for data prep. net_eur, the net value in euros, is just a copy of amount_eur for now.
  2. Select Summarize, the last block. It reads North 6,010, South 5,900: the gross picture.
  3. Select Formula. Select the Expression field, select all its text with Ctrl+A or Cmd+A, and type amount_eur * (1 - rebate_pct / 100). Then select the Formula block. Red text under Expression means a name is mistyped.
  4. Read the preview. Starhopper's first invoice drops from 1,240 to 1,116, and Wonka's doesn't move. Scroll to Wonka's NULL price: net_eur stays NULL.
  5. Select Summarize. One region has changed, and it's enough.

Swipe sideways to see the canvas, or open this page on a larger screen to work with it.

In an expression, press Esc to apply it and move on (press it twice if a suggestion list is open).

The regions you should see

North €5,578, South €5,900. Total €11,478, and South leads by €322.

A calculated column can change the conclusion

This is the number the course has been working toward: net booked invoice value, €11,478, with South ahead. From here on, call it net value.

It also explains both reports. Sydney Swell in Finance has the right figure for North, but her South is €4,590 because her lookup lost Initech's €3,000 and counted Dunder Mifflin's €1,690 in both regions. Her total, €10,168, is close and names the wrong region. Don Tremendous in Sales is furthest off and names the right one by accident, since his old territory map still puts Dunder Mifflin in South.

A calculated column is a decision, so its definition has to travel with it. See the Formula reference.

Common formula mistakes

  • Expecting a null to act like zero. In SQL and Strata, arithmetic on an unknown gives an unknown, so Wonka's unpriced invoice stays NULL. Excel differs, treating an empty cell as 0, so the same formula shows a confident 0.
  • Calculating after you aggregate. A rebate applied to a regional total can't see which customer earned it.
  • Dividing whole numbers. In PostgreSQL and SQL Server, 5 / 100 is 0, so write 100.0.
  • Typing a rate into the formula. Keep it in a column or a table, where someone can see and change it.

The same formula in SQL and Excel

SELECT invoice_id,
       amount_eur * (1 - rebate_pct / 100.0) AS net_eur
FROM invoices

In Excel, type =C2*(1-D2/100) and fill it down. Then group the result with SUM in SQL, or with a PivotTable in Excel.