Only show reliable averages with IF
An average over two order lines says little. A common report rule: only show the average when there is enough data, and leave the cell empty otherwise.
IF(condition, value) without a third argument returns BLANK when the condition is false. A matrix hides rows where every value is BLANK, so the rule also tidies the report.
Your task
Write Reliable Avg Price: the average Sales[Unit Price] of the cell, but only when the cell has at least 10 order lines; otherwise BLANK.
About this exercise
You practise returning blank on purpose with IF, COUNTROWS and AVERAGE. The exercise runs on a small bike-shop model with a Sales fact table and Product, Customer and Date dimensions, the same star schema in every lesson.
Your measure, Reliable Avg Price, fills a Power BI-style matrix grouped by Customer[Customer] and Date[Year]. As in a real report, each cell evaluates the measure in its own filter context, and the subtotals and the grand total are evaluated again in theirs rather than added up. When you check your answer, every cell is compared with the expected value, wrong cells are marked, and the hint is about the mistake you made.
Measures that already exist in the model: Sales Amount. Reference them in square brackets, for example [Sales Amount].
Where you start
Reliable Avg Price =The result to build
| Year | |||
|---|---|---|---|
| Customer | 2024 | 2025 | Total |
| Anna Berger | 337.20 | 228.27 | 291.12 |
| Cycles Marchand | 325.28 | 167.32 | 243.14 |
| Emma Clarke | |||
| Fietsen De Vries | 259.39 | 346.73 | 305.14 |
| Jonas Weber | 343.64 | 452.43 | |
| Léa Moreau | 135.10 | 260.20 | 197.65 |
| Northside Bikes | 333.84 | 215.39 | 280.39 |
| Oliver Hughes | 41.00 | 206.00 | |
| Sanne Bakker | 31.00 | 234.72 | |
| Velo Haus | 211.41 | 334.82 | 274.41 |
| Total | 279.05 | 280.23 | 279.63 |
Last updated 10 November 2026
Opening the exercise…