Store report with subtotals
A printed-style sales report: region shown once per block, with subtotal and total rows.
What's messy
- Region only on the first row of each block
- Subtotal and Grand Total rows
Columns
| Column | Type | Note |
|---|---|---|
| Region | text | Fill down |
| Store | text | Includes Subtotal/Grand Total |
| Sales | whole number |
Try these
- Fill Region down
- Remove Subtotal and Grand Total rows
How to approach it
Use Fill Down on Region so every store row carries its region, since the label is printed only on the first row of each block. Then remove the Subtotal and Grand Total rows: they are totals of rows already in the table and would double the figures if left in. Filter on those exact labels in the Store column rather than removing rows by position, so the query still works when a region gains a store. Check the result by summing Sales and comparing it with the Grand Total in the original report.
Preview (first 8 rows)
| Region | Store | Sales |
|---|---|---|
| North | Harbour St 1 | 837 |
| null | Station Rd 2 | 6668 |
| null | Subtotal | 7505 |
| South | Harbour St 4 | 1167 |
| null | Mill Lane 5 | 5214 |
| null | Harbour St 6 | 820 |
| null | Subtotal | 7201 |
| East | Mill Lane 8 | 5567 |
All data is fictional. CSV cells that could be read as spreadsheet formulas are prefixed with an apostrophe.