Customers above average with ADDCOLUMNS
ADDCOLUMNS(VALUES(Customer[Customer]), "S", [Sales Amount]) returns the customers of the cell with a computed column S. Later expressions read it as [S].
Compute the cell’s average per customer once, in a variable, then count the customers whose S is above it.
Your task
Write Above Average: the number of customers whose Sales Amount in the cell is above the cell’s average Sales Amount per buying customer.
About this exercise
You practise virtual columns with ADDCOLUMNS, VALUES, FILTER and VAR. 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, Above Average, fills a Power BI-style matrix grouped by Product[Category] 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
Above Average =
VAR Avg = AVERAGEX(VALUES(Customer[Customer]), [Sales Amount])
RETURNThe result to build
| Year | |||
|---|---|---|---|
| Category | 2024 | 2025 | Total |
| Accessories | 4 | 4 | 4 |
| Bikes | 4 | 4 | 4 |
| Clothing | 3 | 2 | 3 |
| Total | 4 | 4 | 4 |
Last updated 10 November 2026
Opening the exercise…