ALL and the share of total
To show each category as a share of all categories, the denominator has to ignore the category on the row but still respect the year in the column.
As a CALCULATE filter, ALL(Product) removes every filter on the Product table. The Year filter stays, so each column adds up to 100%.
Mind which table you clear: ALL(Sales) removes filters on Sales and on every table Sales points to, including Date.
Your task
Write a measure named Category Share: the sales amount of the cell divided by the sales amount of all products in the same year.
About this exercise
You practise removing filters with all with ALL. 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, Category Share, 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 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
Category Share = DIVIDE([Sales Amount], CALCULATE([Sales Amount], ALL(Product)))ALL inside CALCULATE removes filters. Choose the table or column carefully: everything else keeps filtering.
The expected result
| Year | |||
|---|---|---|---|
| Category | 2024 | 2025 | Total |
| Accessories | 11.0% | 10.4% | 10.7% |
| Bikes | 80.2% | 82.7% | 81.4% |
| Clothing | 8.8% | 6.9% | 7.9% |
| Total | 100.0% | 100.0% | 100.0% |
Common mistakes
Category Share = DIVIDE([Sales Amount], [Sales Amount])Numerator and denominator see the same filters, so every cell is 100%. Remove the product filter in the denominator with CALCULATE(..., ALL(Product)).
Category Share = DIVIDE([Sales Amount], CALCULATE([Sales Amount], ALL(Sales)))ALL(Sales) also removes the Year filter, because filters on Date reach Sales through the relationship. Clear only Product.
Category Share = DIVIDE([Sales Amount], CALCULATE([Sales Amount], ALL('Date')))That divides by all years of the same category: the share of each year, not of each category. Clear Product instead of Date.
Hints
- The denominator is CALCULATE([Sales Amount], ...).
- Which filter should the denominator ignore? The one coming from the Product table.
Opening the exercise…