ALLEXCEPT: share within a group
This matrix nests products under categories. Each product row is filtered by its category and by its product name.
ALLEXCEPT(Product, Product[Category]) removes every filter on Product except the one on Product[Category]. In the denominator, that leaves the sales of the whole category.
ALLEXCEPT only keeps filters that exist. It works here because Category is on the rows.
Your task
Write a measure named Share in Category: each product's sales amount divided by the sales amount of its category.
About this exercise
You practise keeping one filter while removing the rest with ALLEXCEPT. 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, Share in Category, fills a Power BI-style matrix grouped by Product[Category] and Product[Product]. 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 and Total Cost. Reference them in square brackets, for example [Sales Amount].
Last updated 10 November 2026
Solution, expected result and common mistakes
Worked solution
Share in Category = DIVIDE([Sales Amount], CALCULATE([Sales Amount], ALLEXCEPT(Product, Product[Category])))ALLEXCEPT is "remove all filters on this table except these columns". The kept column must actually be filtered for it to matter.
The expected result
| Category › Product | Share in Category |
|---|---|
| Accessories | 100.0% |
| Bike Lock | 25.4% |
| Helmet | 62.9% |
| Water Bottle | 11.6% |
| Bikes | 100.0% |
| City Bike | 29.9% |
| Road Bike | 37.5% |
| Trail Bike | 32.6% |
| Clothing | 100.0% |
| Cycling Jersey | 50.7% |
| Rain Jacket | 49.3% |
| Total | 100.0% |
Common mistakes
Share in Category = DIVIDE([Sales Amount], CALCULATE([Sales Amount], ALL(Product)))ALL(Product) also removes the category, so you get each product's share of all sales. Keep the category filter with ALLEXCEPT(Product, Product[Category]).
Share in Category = DIVIDE([Sales Amount], CALCULATE([Sales Amount], ALLEXCEPT(Product, Product[Product])))That keeps the product filter and removes the category: every product is 100%. Keep Product[Category] instead.
Share in Category = DIVIDE([Sales Amount], [Sales Amount])Both sides see the same filters, so every row is 100%. The denominator needs CALCULATE with ALLEXCEPT.
Hints
- Start from the previous lesson: DIVIDE([Sales Amount], CALCULATE([Sales Amount], ...)).
- ALLEXCEPT(Product, Product[Category]) clears Product but keeps the category.
Opening the exercise…