The request
From Brian Foster, Lighting Buyer
“My page has a Category slicer set to Lighting and a matrix of subcategories. I need each subcategory's share of Lighting, not of the whole store: pendants against all lighting. Other buyers will reuse the page with their own category, so it has to follow the slicer.”
Your goal
The matrix has products[subcategory] on its rows and a products[category] slicer set to Lighting. Create a measure called Share of Category: the subcategory's Sales divided by Sales for its whole category. Remove every product filter except the category one, with ALLEXCEPT(products, products[category]) or REMOVEFILTERS(products[subcategory]).
- Fill
[Share of Category]on every row of the visualnot done yet - The Total row right toonot done yet
Your measure
- Visual
- A matrix: rows = products[subcategory]; values = [Sales], [Share of Category]
- Slicers
- products[category] = Lighting
- Measures in the report
Sales = CALCULATE(SUM(order_items[line_total]), orders[status] = "completed")
Data pane: tables and columns
- order_items order_item_id, order_id, product_id, quantity, unit_price, discount_pct, line_total
- orders order_id, customer_id, order_date, status, channel, shipping_fee, coupon_code
- products product_id, name, category, subcategory, unit_price, unit_cost, launched_on, is_active
- customers customer_id, first_name, last_name, email, city, state, signup_date, segment, referral_source
- returns return_id, order_item_id, returned_on, reason, refund_amount
- Date Date, Year, Quarter, Month Number, Month, Year Month, Year Quarter, Day, Weekday Number, Weekday
Loading the editor…
Reference solution
| subcategory | Sales | Share of Category |
|---|---|---|
| Floor Lamps | $93,964 | |
| Pendants | $392,054 | |
| Sconces | $312,965 | |
| Table Lamps | $202,861 | |
| Total | $1,001,844 |
Mote checks your result
Press Check. Mote fills in your answer and compares it with what the report needs.
What Brian gets, and why
The lesson
The last level divided by the whole store. Brian needs a narrower denominator: the parent category of each subcategory. That means removing some product filters and keeping others.
What you are working in
This page runs a practice DAX engine built for this site. It evaluates your measure for each
subcategory row, with the Lighting slicer applied, and checks every cell. In Power BI Desktop:
Home → New measure, type the formula, set Measure tools → Format → Percentage, and add a
slicer with products[category].
Three ways to remove filters
| Modifier | Removes | Keeps |
|---|---|---|
ALL(products) |
every filter on products |
nothing on products |
ALLEXCEPT(products, products[category]) |
every filter on products |
the category filter |
REMOVEFILTERS(products[subcategory]) |
the subcategory filter | everything else |
REMOVEFILTERS does exactly what ALL does inside CALCULATE; the newer name says what it is
for. ALL can also return a table of every value (COUNTROWS(ALL(products))), which
REMOVEFILTERS can’t.
The measure
Share of Category =
DIVIDE(
[Sales],
CALCULATE([Sales], ALLEXCEPT(products, products[category]))
)For the Pendants row the cell’s filter context is: category = Lighting (slicer), subcategory = Pendants (row). ALLEXCEPT removes the subcategory filter and keeps the category one, so the denominator is all Lighting sales, about $1.0 million. Pendants sold about $392,000, so the share is 39.1%. The four Lighting rows add up to 100%.
The same result written the other way:
Share of Category =
DIVIDE([Sales], CALCULATE([Sales], REMOVEFILTERS(products[subcategory])))Which one to choose? ALLEXCEPT says “keep only this”, which stays right if someone later adds the
product name to the matrix. REMOVEFILTERS says “drop only this”, which keeps a filter on, say,
products[is_active] that a page filter might set. Pick by what the ticket says must survive.
What happens with ALL(products)
ALL wipes the slicer too, so the denominator becomes all store sales (about $5.9 million) and Pendants shows 6.7%. The numbers look plausible, which is why this bug survives into published reports. Always check that the shares add up to 100% where they should.
Common mistakes
- ALL(products[category]). It removes the one filter you wanted to keep and leaves the subcategory filter, so every row shows 100%.
- Several categories in the slicer. ALLEXCEPT keeps the slicer as it is, so with Lighting and Outdoor both ticked the denominator is both categories together. A matrix with category as an outer row fixes that: each row then has one category.
Lantern lit!
Practised: allexceptremovefilterspercent of parentcalculatedivide
- Solved
- No hints
- First run