Calculate customer repeat rate without double-counting visits

Repeat-customer rate needs a definition of both customer and repeat occasion. In an order-line table, two rows can belong to one order. Counting rows as visits or purchases can therefore classify a one-order customer as a repeat buyer.

This lesson defines a repeat buyer as a known customer with at least two distinct Paid OrderIDs inside the selected observation window. It does not measure website visits, lifetime repeat behaviour or continuous retention.

Inspect the qualifying order history

In the retail lab, customer 1 has Paid orders O1 and O3. O1 contains two lines, but it is one purchase occasion under this definition. Customer 2 has O2 and O5. Customer 4 has O6.

The explicit Unknown member has O7 but is excluded from known-customer counts. There are three known Paid buyers, of whom two have at least two distinct Paid orders. The full-window repeat rate is therefore 2/3, approximately 66.67%.

Count distinct orders within each customer

Create Paid Orders and Known Paid Customers from the measure contracts, then define:

Known Repeat Paid Customers =
COALESCE(
    COUNTROWS(
        FILTER(
            VALUES(DimCustomer[CustomerKey]),
            DimCustomer[CustomerKey] <> 0
                && CALCULATE([Paid Orders]) >= 2
        )
    ),
    0
)

Known Repeat Customer Rate =
DIVIDE([Known Repeat Paid Customers], [Known Paid Customers])

Create each definition as a separate measure. The iteration considers customer members, and evaluation of Paid Orders under each customer identifies those meeting the threshold. The dimension-to-fact relationship must be the one described in the lab.

DISTINCTCOUNT's nonadditive behaviour is documented in Microsoft's reference; CALCULATE's context behaviour is documented here.

Test January separately

January has known Paid buyers 1 and 2. Customer 1 has two distinct orders, while customer 2 has one. The January repeat rate is 1/2, or 50%.

A line-count rule would see customer 1's three January lines, but the correct qualifying count is two orders. To expose the error more sharply, filter the data to O1 alone: two lines must still represent one order and must not satisfy the repeat threshold.

Order identity itself must be reliable. If several systems reuse OrderID values, qualify the key by source or another business identifier before counting distinct orders.

Define what slicers do to the window

As written, the measure respects the selected date and product context. A Software-only selection asks whether a customer has at least two qualifying Software orders in that selected window, not whether they are a repeat buyer anywhere in their history.

If the business wants lifetime repeat status among current-period buyers, that is a different measure with a separate historical population. State which filters define the buyer cohort and which define the repeat evidence before removing any date filters.

Do not compare these variants under the same label.

Separate repeat purchase from retention

A customer can place two orders in January and never return afterward, yet qualify as a repeat buyer in January. Monthly retention asks whether an acquisition or prior-activity group is active in a later period.

Repeat rate also depends on observation opportunity. A newly acquired customer near the end of the window has less time to make a second purchase than an earlier customer. Consider acquisition cohorts or time-to-repeat analysis when that difference matters.

The fixture is too small and synthetic to support claims about customer loyalty or marketing effectiveness.

Verify numerator and denominator together

Display qualifying customers and distinct order counts in a development table. Confirm keys 1 and 2 in the full-window numerator and keys 1, 2 and 4 in the denominator. Keep Unknown value visible in separate monetary quality measures.

Python/SQLite verifies source records and counts; the DAX iterator and visual context require Power BI application review.

Exercise: add another line to O6 without creating a new OrderID. Customer 4 must remain a one-order buyer. Then add O8 for customer 4 and verify that the repeat numerator increases while the known-buyer denominator stays three.

NeuraPath's Data Analytics with Generative AI course connects customer metrics with transaction grain. A trustworthy repeat-rate measure counts the intended occasions and makes its observation window explicit.

Continue learning

This article is part of the DAX measures and analytical correctness sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in DAX inactive relationships and order versus delivery dates.
• Continue with DAX cohort measures: preserve the acquisition group.