Average per customer and year with SUMMARIZE
SUMMARIZE(Sales, Customer[Customer], Date[Year]) returns one row per combination of customer and year that has sales. It is a grouping done inside the measure, not in the report.
Iterating it with AVERAGEX averages the Sales Amount per customer-year, which is a different number from the average per customer when customers buy in several years.
Your task
Write Avg per Customer-Year: the average Sales Amount per combination of customer and year with sales.
About this exercise
You practise grouping in a measure with SUMMARIZE and AVERAGEX. 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, Avg per Customer-Year, fills a Power BI-style matrix grouped by Customer[Segment] and Product[Category]. 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
Avg per Customer-Year =The result to build
| Category | ||||
|---|---|---|---|---|
| Segment | Accessories | Bikes | Clothing | Total |
| Business | 3,056.36 | 22,325.75 | 2,045.44 | 27,427.56 |
| Consumer | 312.00 | 3,444.44 | 428.22 | 3,452.18 |
| Total | 1,531.72 | 12,329.76 | 1,189.27 | 13,547.08 |
Last updated 10 November 2026
Opening the exercise…