Group by columns in different tables

[tetst3tetsteta1]

I want to calculate a usage rate by sales rep by month which is number of accounts that used a service / number of sales rep's accounts

My data looks like

Account table 

AccountID	Sales Rep
1	David
2	David
3	David

SubAccount (note multiple sub accounts per account)

SubAccountID	AccountID
A	1
B	1
C	2
D	3

Visits table at the subaccount level

SubAccountID	Visit Date	VisitID
A	6/2/24	123
B	6/3/24	124
C	6/4/24	125

-end of table-

I want the final table to look like:

Report Month	Sales Rep	Number of Accounts with a Visit	Number of Accounts	Usage Rate
June 2024	David	2	3	66%
...	...	...	...

David had 3 total visits, but only 2 account ids with a visit and 3 account ids in David's book of accounts. 1 of his accounts had a visit from each subaccount, but that should only be counted once per account.

How do I make this?

Thank you in advance for your time

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

Number of visit by Account = 
COUNTROWS ( SUMMARIZE ( Visit, Account[AccountID] ) )

Number of Account = 
IF ( NOT ISBLANK ( [Number of visit by Account] ), COUNTROWS ( Account ) )

usage rate = 
DIVIDE ( [Number of visit by Account], [Number of Account] )