Opioids prescribed per Emergency Department visit

[Margaret]

I need to calculate the rate at which various providers prescribe controlled substances:

For each provider: Count(Rx)  /  Count(Visits)  

Sometimes a provider prescribes a medication to a patient they aren't assigned to.

Sometimes more than one provider is assigned to a single patient.

Here are the two tables I have:   

Account Number	Rx Provider	Medication	Rx ID
12667	Brown, PA	HYDROmorphone HCL	15213627357
12667	Brown, PA	HYDROmorphone HCL	15233728439
12667	Dr. McDonald	fentaNYL	15182933689-2
12667	Dr. McDonald	HYDROCO37|Hydrocodone	15194001371
12667	Dr. McDonald	HYDROCOD/APAP 5/325	15193742325
12667	Dr. McDonald	HYDROmorphone HCL	15182933689
27025	Dr. Rodriguez	chlordiazePOXIDE 25MG	29011739391
27025	Dr. Wong	LORazepam	28222549500-2
27025	Dr. Wilson	LORazepam	28232607599

Account Number	Visit Provider
12667	Brown, PA
27025	Dr. Rodriguez
27025	Dr Wong
27025	Dr Wilson

I would like to create an object for the end user that looks like this:    

Provider	Rx's	Visits	Rx's/Visit
Brown, PA	2	1	2/1
Dr. McDonald	4	0	4/0
Dr. Rodriguez	1	1	1/1
Dr. Wong	1	1	1/1
Dr. Wilson	1	1	1/1

(I also need to make a line graph showing Providers Rx/Visit rate over time with MonthYear as the X axis--so if I end up having to count providers visits in the script, somehow I would have to relate it back to my Calendar table.)

[Margaret]

Yes 😞

[Anonymous]

Try using the Total() function.  I had something similar with months and used it like this:

count({<Year={'$(=max(Year))'}>}TOTAL<DispMonth> Distinct ACCOUNT_ID).

I was getting the same count for each month but this separated it into counts for each individual month.