Average products per customer
How many different products does a customer buy, on average? Iterate the customers, count each one’s distinct products in Sales, and average.
The count has to run per customer, so it needs context transition: CALCULATE(DISTINCTCOUNT(Sales[ProductKey])). Customers who bought nothing return BLANK and drop out of the average.
Your task
Write Products per Customer: the average number of different products per customer who bought in the cell.
About this exercise
You practise counting across a relationship with AVERAGEX, CALCULATE and DISTINCTCOUNT. 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, Products per Customer, fills a Power BI-style matrix grouped by Customer[Country] 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
Products per Customer =The result to build
| Year | |||
|---|---|---|---|
| Country | 2024 | 2025 | Total |
| France | 6.00 | 6.00 | 7.50 |
| Germany | 7.00 | 7.00 | 8.00 |
| Netherlands | 6.00 | 5.50 | 7.50 |
| United Kingdom | 6.50 | 4.00 | 5.33 |
| Total | 6.44 | 5.60 | 7.00 |
Last updated 10 November 2026
Opening the exercise…