The request
From Megan Collins, Head of Sales
“We're deciding whether to hire a second B2B rep and which categories they'd own. For 2025, per category: how much business customers bought, what share of the category's sales that is, and how each category splits the business total. The page already has a Year slicer on 2025.”
Your goal
Step 1 of 3. Create a measure called Business Sales: Sales for customers whose segment is business.
- Fill
[Business Sales]on every row of the visualnot done yet - The Total row right toonot done yet
Step 2 of 3. Keep Business Sales and add a measure called Business Mix: Business Sales divided by Sales, with DIVIDE. It is the business share of each category's own sales.
- Fill
[Business Mix]on every row of the visualnot done yet - The Total row right toonot done yet
Step 3 of 3. Keep both measures and add Share of Business: each category's Business Sales divided by Business Sales for all categories, still for 2025. Remove only the product filters, with REMOVEFILTERS(products) (or ALL(products)).
- Fill
[Share of Business]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], [Business Sales]
- Slicers
- 'Date'[Year] = 2025
- 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 | Business Sales |
|---|---|---|
| Decor | $290,301 | |
| Furniture | $573,131 | |
| Kitchen | $292,808 | |
| Lighting | $385,145 | |
| Outdoor | $507,358 | |
| Textiles | $251,426 | |
| Total | $2,300,170 |
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], [Business Mix]
- Slicers
- 'Date'[Year] = 2025
- 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 | Business Mix |
|---|---|---|
| Decor | $290,301 | |
| Furniture | $573,131 | |
| Kitchen | $292,808 | |
| Lighting | $385,145 | |
| Outdoor | $507,358 | |
| Textiles | $251,426 | |
| Total | $2,300,170 |
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 of Business]
- Slicers
- 'Date'[Year] = 2025
- 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 Business |
|---|---|---|
| Decor | $290,301 | |
| Furniture | $573,131 | |
| Kitchen | $292,808 | |
| Lighting | $385,145 | |
| Outdoor | $507,358 | |
| Textiles | $251,426 | |
| Total | $2,300,170 |
Mote checks your result
Press Check. Mote fills in your answer and compares it with what the report needs.
What Megan gets, and why
The lesson
Megan’s three numbers are the three moves of this chapter in one report: add a filter, divide in the same context, and divide by a denominator with some filters removed.
What you are working in
This page runs a practice DAX engine built for this site. Each step evaluates your newest measure in the matrix (categories on the rows, 2025 from the slicer) and checks every cell. Keep the measures from earlier steps in the editor: later ones use them. In Power BI Desktop each one is a separate Home → New measure; to keep a report tidy, create them in one table and group them in a display folder (Model view → Properties → Display folder).
Step 1: add a filter
Business Sales = CALCULATE([Sales], customers[segment] = "business")The filter sits on customers. It flows to orders and from there to order_items, and it
combines with the category row and the year slicer, because they are all on different columns.
Step 2: divide in the same context
Business Mix = DIVIDE([Business Sales], [Sales])Both measures see the same category and year, so the result is a share inside each category:
of everything Furniture sold in 2025, how much went to businesses. Use DIVIDE for every ratio:
it returns a blank instead of an error when the denominator is empty.
Step 3: change the denominator
The last number asks a different question: of all 2025 business sales, how much is Furniture? The denominator must ignore the category row but keep the year slicer and the segment filter.
Share of Business =
DIVIDE(
[Business Sales],
CALCULATE([Business Sales], REMOVEFILTERS(products))
)REMOVEFILTERS(products) removes every filter on the products table and leaves all others.
ALL(products) does the same inside CALCULATE. The six rows now add up to 100%.
Reading the two percentages together
Business Mix says how much a category depends on businesses (around half, everywhere). Share of Business says how much of the business book a category holds (Furniture and Outdoor lead). A hiring decision needs the second; a pricing decision for a category needs the first. Showing both side by side, with clear names, saves a meeting.
Common mistakes
- Removing every filter.
REMOVEFILTERS()with no argument also drops the 2025 slicer, and the denominator becomes three years of business sales. - ALL on the wrong column.
ALL(customers[segment])changes nothing: Business Sales puts the business filter straight back, the category row stays, and every row shows 100%. - Mixing up the two shares. Name measures for the question they answer, not “Pct 1” and “Pct 2”.
Lantern lit!
Practised: calculatedivideremovefilterspercent of totalfilter context
- Solved
- No hints
- First run