The request
From Brian Foster, Head of Mobile App
“I'm presenting the app channel next week. On the channel page I'll pick app in the slicer and walk through the categories. I need to show two things per category: how app's own sales split across categories, and how much of each category's sales comes through the app. And a label I can read out that says which channel this is and its share.”
Your goal
Step 1 of 3. Create a measure called Category Share: each category's Sales divided by the Sales of all categories, for the channel and year the slicers select. On the Total row it is 100%.
- Fill
[Category Share]on every row of the visualnot done yet - The Total row right toonot done yet
Step 2 of 3. Add a measure called Channel Share: each category's Sales in the selected channel divided by that category's Sales across all channels, still in 2025. Keep Category Share in the editor.
- Fill
[Channel Share]on every row of the visualnot done yet - The Total row right toonot done yet
Step 3 of 3. Add a measure called Share Label that returns the selected channel, then ": ", then Channel Share formatted as "0.0%", then " of sales", for example "app: 43.3% of sales". When there isn't exactly one channel selected it starts with "All channels" instead. Keep the other two measures.
- Fill
[Share Label]on every row of the visualnot done yet - The Total row right toonot done yet
Step 1 · your measure
- Visual
- A matrix: rows = products[category]; values = [Sales], [Category Share]
- Slicers
- 'Date'[Year] = 2025orders[channel] = app
- 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 | Category Share |
|---|---|---|
| Decor | 127,955.33 | |
| Furniture | 215,193.5 | |
| Kitchen | 133,000.84 | |
| Lighting | 174,926.47 | |
| Outdoor | 227,093.18 | |
| Textiles | 117,354.86 | |
| Total | 995,524.18 |
Mote checks your result
Press Check. Mote fills in your answer and compares it with what the report needs.
Step 2 · your measure
- Visual
- A matrix: rows = products[category]; values = [Sales], [Channel Share]
- Slicers
- 'Date'[Year] = 2025orders[channel] = app
- 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 | Channel Share |
|---|---|---|
| Decor | 127,955.33 | |
| Furniture | 215,193.5 | |
| Kitchen | 133,000.84 | |
| Lighting | 174,926.47 | |
| Outdoor | 227,093.18 | |
| Textiles | 117,354.86 | |
| Total | 995,524.18 |
Mote checks your result
Press Check. Mote fills in your answer and compares it with what the report needs.
Step 3 · your measure
- Visual
- A matrix: rows = products[category]; values = [Sales], [Share Label]
- Slicers
- 'Date'[Year] = 2025orders[channel] = app
- 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 Label |
|---|---|---|
| Decor | 127,955.33 | |
| Furniture | 215,193.5 | |
| Kitchen | 133,000.84 | |
| Lighting | 174,926.47 | |
| Outdoor | 227,093.18 | |
| Textiles | 117,354.86 | |
| Total | 995,524.18 |
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
This boss puts the chapter together on one page: slicers that filter everything, two measures that each ignore exactly one filter on purpose, and a label that says what the reader selected.
What you are working in
A practice report, not Power BI, which runs only on Windows. The slicers (year 2025, channel app) are listed above the matrix of categories, and a DAX engine built for this site evaluates your measures in every cell. Each step checks the last measure in the editor; keep the earlier ones.
In Power BI Desktop you’d add a Slicer for orders[channel] and one for 'Date'[Year], create
each measure with Home → New measure, and format the shares with Measure tools → Format → Percentage while the measure is selected. The label goes in a card, or into a
visual’s title with Format visual → General → Title → fx → Field value.
Two shares, two denominators
Both shares divide [Sales] by a bigger Sales. The only difference is which filter the bigger
one drops:
| measure | keeps | removes | answers |
|---|---|---|---|
| Category Share | channel, year | category | where do app’s sales come from? |
| Channel Share | category, year | channel | how much of each category goes through the app? |
Category Share = DIVIDE([Sales], CALCULATE([Sales], REMOVEFILTERS(products[category])))Because neither measure names the year, the year slicer reaches both denominators. Switch the slicer to 2024 and both shares are for 2024; switch channel to web and both are about web. That’s what “slicer-aware” means: the measure only overrides what it has to.
DIVIDE instead of / returns BLANK when the denominator is empty or zero (a channel with no sales in a
category) instead of Infinity or NaN.
The numbers for app in 2025
| category | app sales | Category Share | Channel Share |
|---|---|---|---|
| Outdoor | $227,093 | 22.8% | 44.8% |
| Furniture | $215,194 | 21.6% | 37.5% |
| Textiles | $117,355 | 11.8% | 46.7% |
| Total | $995,524 | 100% | 43.3% |
Furniture is a big part of what the app sells, yet the app’s weakest category relative to the other channels: a useful point for Brian’s talk.
The label
Share Label = SELECTEDVALUE(orders[channel], "All channels") & ": " & FORMAT([Channel Share], "0.0%") & " of sales"FORMAT turns a number into text with a format string: "0.0%" multiplies by 100 and adds one
decimal and the percent sign. Text measures can’t be summed or charted, but they are exactly what
a card or a dynamic title needs.
Common mistakes
- Removing a table instead of a column.
ALL(orders)also drops the year that reaches orders through the Date table. - Swapping the denominators. If the Total row of Category Share isn’t 100%, the wrong filter was removed.
- Dividing with
/. Fine until a slicer leaves a denominator empty and a cell shows Infinity.
Lantern lit!
Practised: slicerremovefiltersdivideselectedvalueformatfilter context
- Solved
- No hints
- First run