BI basics, episode 3 of 6
Measures and dimensions
The numbers you add up, and the labels you slice them by.
Every business question has the same grammar. Learn the two parts of it and you can turn almost any request into something a tool can answer.
The two parts
A measure is a number you calculate. Sales amount, number of orders, average spend. Measures are added up, counted or averaged.
A dimension is a label you group by. Date, café, product, category. Dimensions are how you slice a measure.
Put them together and you get a question:
- Sales by café
- Orders by month
- Average spend by day of week by café
Listen for the word “by”. Whatever comes before it is a measure. Whatever comes after is a dimension.
From table to answer
Start from the sales table in the last episode. To answer “sales by café”, a tool does three things: it groups the rows by café, adds up the Amount in each group, and shows one line per café.
| Café | Sales |
|---|---|
| Harbour | 12.50 |
| Old Town | 19.50 |
Nothing in that result was typed by a person. Change the dimension to Product and the same measure gives a different answer. This is why tidy tables matter: one table, many questions.
Ways to calculate
The same column can produce several measures.
- Sum: total sales.
- Count: how many sales lines.
- Distinct count: how many different products sold.
- Average: typical amount per line.
- Minimum and maximum: the smallest and largest sale.
Choosing the wrong one is a classic beginner mistake. “How many customers?” is a distinct count of customers, not a count of rows. One customer who buys three times is still one customer.
Numbers that should not be added
Not every number is a measure you can sum.
- Prices and rates. Adding the price of a flat white to the price of a croissant means nothing. Average them, or better, divide total sales by total quantity.
- Balances. Stock on hand on Monday plus stock on hand on Tuesday is not stock. For a balance, take the latest value.
- Percentages. Never add them and be careful averaging them. An average of averages hides the size of each group. Recalculate from the totals.
A simple check: say the result aloud. “Total price of all products” sounds wrong because it is wrong.
Why definitions matter
Two people can use the same word for different measures. “Sales” might include tax or not, count refunds or not, use the order date or the payment date. Neither is a mistake, but a report must pick one and say which. A good measure comes with a one-line definition that a new colleague could follow.
Try it
Write five questions your team asks regularly. Rewrite each as measure by dimension. Then, for each measure, write whether it is a sum, a count, a distinct count or an average.
If a question will not fit the pattern, it is usually two questions in one. Split it.
Next: once the numbers are on a chart, how to read it without being fooled.