Count how many customers only ordered once

ChrisZimmerman · October 23, 2020, 3:53pm

I am trying to figure out the percentage of agents (customers) that have only ordered 1 inspection (an inspection for us is an order) per franchise location.

We have an orders table. I have a measure that calculates the inspections [Inspections = COUNT(‘Orders Table’[oid])] where the OID is the Order ID. How do I determine the percentage of agents that only ordered once (1 order agents / all agents where inspection count is greater than 0)?

Desired output will be a KPI that shows percentage of ‘One Off’ agents per location.

I have include a sample of Orders Table, Agents Table, Franchise Table and a table called ISN ID that links them together.

Agents Table.xlsx (127.8 KB) ISN ID.xlsx (9.6 KB) Franchise Table.xlsx (9.6 KB) Orders Table.xlsx (77.4 KB)

BrianJ · October 23, 2020, 5:12pm

@ChrisZimmerman,

See if this works for you:

Here’s the measure that does most of the heavy lifting:

One Offs Count = 

VAR vTable =
ADDCOLUMNS(
    SUMMARIZE(
        'Order File',
       'Order File'[buyersagent]
    ),
    "@InspCount", [Count Inspections]
)

VAR FiltvTable =
FILTER(
    vTable,
    [@InspCount] = 1
)

VAR Result =
COUNTROWS( FiltvTable )

RETURN
Result

Note that I merged your franchise table with your bridge table to create a standard star schema: