9. Lighthouse

Level 4 · Count customers with DISTINCTCOUNT

From Andrew Hughes, Customer Insights Analyst

“For the channel review I already have completed orders per channel. What I'm missing is how many different customers bought through each one: web, app and marketplace. A customer with ten orders counts once.”

Your goal

The page is filtered to completed orders and the matrix has orders[channel] on its rows, next to the given Orders measure. Create a measure called Customers that counts the distinct customers who placed those orders.

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

Your measure

DAX
Visual
A matrix: rows = orders[channel]; values = [Orders], [Customers]
Slicers
orders[status] = completed
Measures in the report
Orders = COUNTROWS(orders)
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[Customers] fills in when you press Check
    channelOrdersCustomers
    app3,885
    marketplace1,597
    web5,367
    Total10,849

    Mote checks your result

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

    The lesson

    “How many orders?” and “how many customers?” sound alike and have different answers. The first counts rows; the second counts the different people behind those rows.

    What you are working in

    This is a practice report built for this site; Power BI Desktop runs only on Windows. The matrix has orders[channel] on its rows and a page filter keeping completed orders only. You write the measure, and the engine evaluates it in every cell, Total included.

    In Power BI Desktop: select the orders table in the Data pane, then Home → New measure (or Table tools → New measure). Type the formula, press Enter, and drag the measure into the matrix’s Values well. The completed-only filter would sit in the Filters pane under Filters on this page.

    Four ways to count

    Function Counts Example
    COUNTROWS(table) rows of a table COUNTROWS(orders): orders
    COUNT(column) non-blank values in a column COUNT(orders[customer_id]): orders that have a customer
    COUNTA(column) non-blank values of any type text columns too
    DISTINCTCOUNT(column) different values in a column DISTINCTCOUNT(orders[customer_id]): customers

    COUNTROWS is the usual way to count rows; it says what you mean and doesn’t depend on any column being filled in. DISTINCTCOUNT is the one for “how many different…”: customers, products, days with sales.

    Customers = DISTINCTCOUNT(orders[customer_id])

    Why the Total is smaller than the rows

    Add up the three channel rows and you get more than the Total. That’s not a bug. A customer who bought on the web and in the app is one of the web customers and one of the app customers, but only one customer in the Total, which is evaluated once over every completed order. Sums add up across rows; distinct counts don’t.

    Counting customers, not orders

    Why not count the customers table? COUNTROWS(customers) would count every customer, buyers or not, because filters on orders don’t flow back to customers (the relationship is single direction, from customers to orders). The customer IDs in orders are exactly the people who placed the filtered orders, so that is the column to count.

    Common mistakes

    • COUNT instead of DISTINCTCOUNT. Counts one per order: the Orders column again.
    • COUNTROWS(customers). The same number on every channel: all customers.
    • Adding up the rows to check the Total. For distinct counts the Total is right as it is.

    Cookie settings

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