Count how many customers only ordered once

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)


See if this works for you:


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

One Offs Count = 

VAR vTable =
        'Order File',
       'Order File'[buyersagent]
    "@InspCount", [Count Inspections]

VAR FiltvTable =
    [@InspCount] = 1

VAR Result =
COUNTROWS( FiltvTable )


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


I hope this is helpful. Full solution file attached below.

– Brian
eDNA Forum - Single Inspection KPI.pbix (176.4 KB)

1 Like

That did it. Great solution. Thank you for the quick response.


Great – glad to hear that worked well for you.

I remember we did another one this spring about inspectors with blanks in future months. You’ve always got interesting questions…

  • Brian