9. Lighthouse

Level 5 · Share of total with ALL

From Rachel Kim, Category Manager

“For the category review I need each category's share of store sales, as a percentage next to the dollars. If Furniture is 24% of everything we sell, I want to see 24%. And it should still add up when someone filters the page to one year.”

Your goal

The matrix has products[category] on its rows and the given measure Sales. Create a measure called Share of Sales: the category's Sales divided by Sales for all products. Use DIVIDE, and CALCULATE with ALL(products) for the denominator.

  • Fill [Share of Sales] on every row of the visual
  • The Total row right too

Your measure

DAX
Visual
A matrix: rows = products[category]; values = [Sales], [Share of Sales]
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…

    Visual[Share of Sales] fills in when you press Check
    categorySalesShare of Sales
    Decor$822,667
    Furniture$1,393,164
    Kitchen$799,150
    Lighting$1,001,844
    Outdoor$1,297,802
    Textiles$579,658
    Total$5,894,284

    Mote checks your result

    Press Check. Mote fills in your answer and compares it with what the report needs.

    The lesson

    A percentage of total is a fraction whose top and bottom live in different filter contexts. The top is the cell’s own Sales. The bottom is the same measure with the category filter taken away. ALL is how you take it away.

    What you are working in

    This page runs a practice DAX engine built for this site, which evaluates your measure in every cell of the matrix and checks it. In Power BI Desktop: Home → New measure, type the formula, then with the measure selected set Measure tools → Format → Percentage so 0.236 shows as 23.6%.

    ALL removes filters

    Inside CALCULATE, ALL(products) is a filter modifier: it removes every filter on the products table (category, subcategory, product name) before the expression is evaluated.

    Share of Sales =
DIVIDE(
    [Sales],
    CALCULATE([Sales], ALL(products))
)

    For the Lighting row:

    • [Sales] sees category = Lighting: about $1.0 million.
    • CALCULATE([Sales], ALL(products)) sees no product filter: about $5.9 million.
    • DIVIDE returns about 0.17, shown as 17.0%.

    On the Total row both halves see no product filter, so the share is 100%.

    ALL on a table or on a column

    ALL(products) clears filters on every column of the table. ALL(products[category]) clears only the category filter, so a subcategory or product filter would survive. For a category matrix both give the same result; the difference shows when a visual has several product columns, which is the next level.

    ALL touches only the table or column you name. A slicer on 'Date'[Year] or on orders[channel] still applies to the denominator, which is what Rachel wants: when the page is filtered to 2025, the shares are shares of 2025 sales and still add up to 100%. To ignore every filter in the report you would write ALL() or REMOVEFILTERS() with no argument, which is rarely what a share needs.

    Why DIVIDE

    DIVIDE(a, b) returns a blank instead of an error when b is zero or blank. Plain / would show “Infinity” or an error in an empty cell. Use DIVIDE for every ratio in a report.

    Common mistakes

    • Same filters on both halves. DIVIDE([Sales], [Sales]) is 100% everywhere.
    • ALL on the fact table. ALL(order_items) removes too much: a fact table’s filters include those of every table it points to (products, orders, customers, Date), so the year slicer goes too and the shares no longer add up to 100% on a filtered page. Remove the filter where it lives.
    • Hard-coding the total. Typing 5,894,284 as the denominator breaks the moment anyone moves a slicer.

    Cookie settings

    Pick what may run on this site. You can change this any time from "Cookie settings" at the bottom of every page.