Forum Discussion
Average for Top Medication per Patient
I'm trying to calculate the avg. number of fills per medication (GCN) for each patient. The caveat is that I only want to consider each patient's most-filled medication. This measure will be displayed in a card visual.
My first measure attempt is below:
Avg. Fills per Top Drug=
AVERAGEX(
//figure out top drug per patient
Summarize(Fact_PatientFills,Fact_PatientFills[Patient ID],'Fact_PatientFills'[GCN],"Top Med",TOPN ( 1, Fact_PatientFills, [Total Fills], DESC))
//figure out avg. number of fills
,_Metrics[Total Fills]
)
Here's some sample data:
Patient ID | Date Filled | GCN |
066B2ED6-9B2E-448B-8F1F-9D8F91882A1E | 1/6/2022 | 9797 |
F83DA74A-BD9A-4319-8CBA-B2B0FD7D3212 | 1/7/2022 | 9797 |
6E1F1A37-2CEE-4DD8-81DD-A88A7E32DADC | 1/13/2022 | 9797 |
F280D919-7683-4B09-80DB-7392732B7691 | 1/11/2022 | 4918 |
41D06333-1BFF-48D9-9B15-4F897B63F9BC | 1/11/2022 | 61740 |
6C4E567F-98D7-4419-AB04-62FB47349F7F | 1/18/2022 | 9797 |
8407677A-47D8-405A-9F1B-F1BF0A72C39D | 1/11/2022 | 13672 |
FB391620-6CDC-4235-99FB-7E9B11AE14D0 | 1/6/2022 | 2842 |
0EF1504D-BF03-4464-B203-9BD6EA15695B | 1/20/2022 | 22123 |
3726D485-5C65-4F3D-9896-C598543EECF4 | 1/18/2022 | 49403 |
79367253-1519-4DC0-ADEE-5F2275D61281 | 1/6/2022 | 68903 |
E90B4B99-F574-4A52-B334-76595F72211E | 1/11/2022 | 49358 |
9825FD3B-A53D-4340-82E3-56452EFF6DF7 | 1/12/2022 | 49365 |
F76F6509-F3C6-4038-815E-16A5FCED368F | 1/3/2022 | 66308 |
C07944A2-65CF-4C08-8B32-0517D82AFD47 | 1/3/2022 | 66308 |
1613CFA5-5C22-43C3-AEDD-8FE7CEE4893B | 1/27/2022 | 49240 |
D16DFC13-7468-4198-A0A4-18113476F882 | 1/10/2022 | 49240 |
2F0A6720-8CDB-4488-AC8C-76AC874C76C8 | 1/31/2022 | 60904 |
2F0A6720-8CDB-4488-AC8C-76AC874C76C8 | 1/3/2022 | 60904 |
02F38354-5D4E-47F4-98F4-D0B68712327F | 1/5/2022 | 60904 |
1 Reply
Hi RossDaBoss00 ,
Based on the data you provided, there seems to be a flaw. Can you further describe your requirements and provide relevant test data so that I can answer your questions as soon as possible.
Looking forward to your reply.
Best Regards,
Henry
