9. Lighthouse

Level 1 · A price band column with IF

From Olivia Bennett, Merchandising Lead

“For the catalogue meeting I want to split our range in two: anything priced at $100 or more is Premium, everything else is Everyday. How many products sit in each group, and how many units has each group sold?”

Your goal

Add a calculated column called Price Band to products: "Premium" when products[unit_price] is 100 or more, otherwise "Everyday". The matrix groups the given measures Products and Units Ordered by your column.

  • Add the column Price Band to products
  • The Total row right too

Your calculated column

DAX
Visual
A matrix: rows = your new column on products, values = [Products], [Units Ordered]
Measures in the report
Products = COUNTROWS(products)Units Ordered = SUM(order_items[quantity])
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…

    VisualData view of products: your column appears in the visual once you press Check
    product_idnamecategorysubcategoryunit_priceunit_cost
    101Vale Velvet LoveseatFurnitureSofas1,659
    102Alder Oak Coffee TableFurnitureTables919503.5
    103Orchard Rattan Dining ChairFurnitureChairs789429.87
    104Upland Oak BookcaseFurnitureStorage429214.7
    105Avery Leather Bed FrameFurnitureBeds449232.7
    106Northfield Rattan SectionalFurnitureSofas22979.35
    107Birch Teak Side TableFurnitureTables16954.61
    108Ashford Leather Dining ChairFurnitureChairs369139.71

    Mote checks your result

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

    The lesson

    A calculated column adds a new column to a table, filled by a DAX formula that runs once for every row. It is the closest thing in Power BI to filling a formula down a column in Excel.

    What you are working in

    Power BI Desktop runs only on Windows, so this is a practice report built for this site: the store’s model, a matrix, and a DAX editor. Your column is added to products, the matrix groups by it, and the engine checks every cell. It follows Power BI’s rules, so the formula works there unchanged.

    In Power BI Desktop: select the products table in the Data pane, then Table tools → New column (or Modeling → New column, or right-click the table → New column). Type the formula in the formula bar and press Enter. The new column appears in Table view filled for every row, and in the Data pane with a small column icon.

    Row context: one row at a time

    When a column formula mentions products[unit_price], it means this row’s price. That is called row context: the formula is evaluated with one current row, then the next, then the next, 120 times for 120 products.

    Price Band = IF(products[unit_price] >= 100, "Premium", "Everyday")

    IF takes three arguments: a test that is TRUE or FALSE, the value when it is TRUE, the value when it is FALSE. Text values go in straight double quotes.

    Grouping by the new column

    Once the column exists, it behaves like any column the data came with. Put products[Price Band] on the rows of a matrix and two rows appear, Everyday and Premium. The measures Products (COUNTROWS(products)) and Units Ordered (SUM(order_items[quantity])) are evaluated per row: the band filters products, the relationship carries the filter to order_items, and each measure counts or sums only that band.

    When the column is calculated

    Calculated columns are computed when the data is loaded or refreshed, and stored in the model like imported columns. They take memory, and they never react to slicers: a product’s band is the same on every page. That is right for a label like this one, which belongs to the product itself.

    Common mistakes

    • The boundary. > 100 and >= 100 differ for every product priced exactly 100. Match the ticket’s words.
    • Curly quotes. “Premium” copied from a document is not valid DAX; use "Premium".
    • No column reference. IF([unit_price] >= 100, …) works inside the products table, but writing products[unit_price] in full makes the formula readable anywhere.
    • A measure instead. A measure has no current row, so products[unit_price] on its own has no single value there. Labels per row are columns.

    Cookie settings

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