Group by columns in different tables

tetst3tetsteta1 — Fri, 14 Jun 2024 15:39:21 GMT

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

Re: Group by columns in different tables

Jihwan_Kim — Sat, 15 Jun 2024 05:57:50 GMT

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] )

topic Group by columns in different tables in DAX Commands and Tips

Group by columns in different tables

Re: Group by columns in different tables