The request
From Eric Lawson, Head of Big-Ticket Categories
“My team looks after Furniture, Lighting and Outdoor, so our page has a slicer with just those three. The share column divides by the whole store, so our shares add up to 63%. I want each category's share of the three I picked, adding up to 100%.”
Your goal
The matrix has products[category] on its rows; a slicer on products[category] selects Furniture, Lighting and Outdoor. Create a measure called Share of Selected: the row's [Sales] divided by [Sales] of all the categories selected in the slicer. The Total row should show 100%.
- Fill
[Share of Selected]on every row of the visualnot done yet - The Total row right toonot done yet
Your measure
- Visual
- A matrix: rows = products[category]; values = [Sales], [Share of Selected]
- Slicers
- products[category] = Furniture, Lighting, Outdoor
- 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
| category | Sales | Share of Selected |
|---|---|---|
| Furniture | $1,393,164 | |
| Lighting | $1,001,844 | |
| Outdoor | $1,297,802 | |
| Total | $3,692,809 |
Mote checks your result
Press Check. Mote fills in your answer and compares it with what the report needs.
What Eric gets, and why
The lesson
A share is a ratio: this row’s number over a bigger number. Everything depends on which bigger number. Eric wants the bigger number to be “everything I picked”, and the slicer is how he picks.
What you are working in
This page is a practice report: Power BI Desktop runs only on Windows, so a DAX engine built for this site evaluates your measure in each cell, following Power BI’s rules. The slicer above the matrix stands for a category slicer on the report page. In Power BI Desktop: Home → New measure, type the formula, then Measure tools → Format → Percentage.
Where a cell’s filters come from
The Lighting cell is filtered on products[category] twice over:
- From outside the visual: the slicer keeps Furniture, Lighting and Outdoor.
- From the visual: the row keeps Lighting.
The numerator, [Sales], uses both: Lighting’s sales. The denominator needs the first filter
and not the second.
ALL versus ALLSELECTED
Share of Total =
DIVIDE([Sales], CALCULATE([Sales], ALL(products[category])))
Share of Selected =
DIVIDE([Sales], CALCULATE([Sales], ALLSELECTED(products[category])))- ALL(products[category]) removes every filter on the column, from the visual and from the slicer. The denominator is all six categories: the whole store. Eric’s three rows add up to 63%.
- ALLSELECTED(products[category]) removes the filters the visual adds (rows, columns) but keeps what was selected outside it: slicers, page and report filters. The denominator is the three selected categories, and the rows add up to 100%.
Both keep filters on other columns. A year slicer would still apply to both versions.
Check it on the Total row
On the Total row there is no row filter, so the numerator is already the three categories’ sales. With ALLSELECTED the denominator is the same, and the Total shows 100%: a quick test that the measure does what you meant. With ALL the Total shows 63%, the selection’s share of the store.
When ALL is the right one
Sometimes the store-wide share is the point: “Lighting is 17% of the company” shouldn’t change because someone filtered the page. Then use ALL, and say so in the column title. Many reports show both columns side by side: share of selection and share of total.
Common mistakes
- ALL when the reader expects the selection. Shares that add up to 63% look like a bug.
- ALLSELECTED on the wrong column.
ALLSELECTED(products[name])removes nothing here: the rows are categories. - Dividing with /. Use DIVIDE so an empty selection gives a blank, not an error.
Lantern lit!
Practised: allselectedallshare of totalslicerdivide
- Solved
- No hints
- First run