Forum Discussion
Distinct Count in a Table
I have the following dataset (part of a large dataset):
Script Number | Product Name | Date Dispensed | Retail Pharmacy | Branch |
|
95104 | PETOGEN (MEDROXYPROGESTERONE) 150MG/ML INJECTABLE | 13:22.0 | Sainsbury | Main Street | |
95105 | CELIB (CELECOXIB) 200MG CAPSULES | 17:12.0 | Sainsbury | Main Street | |
95105 | ELTROXIN 0.05MG TABLETS | 17:12.0 | Sainsbury | Main Street | |
95105 | NORFLEX CO TABLETS | 17:12.0 | Sainsbury | Main Street | |
95106 | ATENOLOL 50MG TABLETS | 42:06.0 | Sainsbury | Main Street | |
95106 | ATENOLOL 50MG TABLETS | 42:06.0 | Sainsbury | Main Street | |
95106 | ATENOLOL 50MG TABLETS | 42:06.0 | Sainsbury | Main Street | |
95106 | LOMANOR (AMLODIPINE) 5MG TABLETS | 42:06.0 | Sainsbury | Main Street | |
95106 | LOMANOR (AMLODIPINE) 5MG TABLETS | 42:06.0 | Sainsbury | Main Street | |
95106 | LOMANOR (AMLODIPINE) 5MG TABLETS | 42:06.0 | Sainsbury | Main Street | |
95106 | BAYER CARDIO (ASPIRIN) 100MG TABLETS | 42:06.0 | Sainsbury | Main Street | |
95106 | BAYER CARDIO (ASPIRIN) 100MG TABLETS | 42:06.0 | Sainsbury | Main Street | |
95106 | BAYER CARDIO (ASPIRIN) 100MG TABLETS | 42:06.0 | Sainsbury | Main Street | |
95106 | KORANDIL (ENALAPRIL MALEATE) 10MG TABLETS | 42:06.0 | Sainsbury | Main Street | |
95106 | KORANDIL (ENALAPRIL MALEATE) 10MG TABLETS | 42:06.0 | Sainsbury | Main Street | |
95106 | KORANDIL (ENALAPRIL MALEATE) 10MG TABLETS | 42:06.0 | Sainsbury | Main Street | |
95106 | LAMIVUDINE 150MG; ZIDOVUDINE 300MG TABLETS | 42:06.0 | Sainsbury | Main Street | |
95106 | LAMIVUDINE 150MG; ZIDOVUDINE 300MG TABLETS | 42:06.0 | Sainsbury | Main Street | |
95106 | LAMIVUDINE 150MG; ZIDOVUDINE 300MG TABLETS | 42:06.0 | Sainsbury | Main Street | |
95106 | NEVIRAPINE 200MG TABLETS | 42:06.0 | Sainsbury | Main Street | |
95106 | NEVIRAPINE 200MG TABLETS | 42:06.0 | Sainsbury | Main Street | |
95106 | NEVIRAPINE 200MG TABLETS | 42:06.0 | Sainsbury | Main Street | |
95107 | FLOMIST (FLUTICASONE DIPROPIONATE) 50MCG/INHALATION NASAL SPRAY | 42:57.0 | Sainsbury | Main Street | |
95108 | CLINDAMYCIN 150MG CAPSULES | 46:33.0 | Sainsbury | Main Street | |
95108 | TAGERA FORTE (SECNIDAZOLE) 1G TABLETS | 46:33.0 | Sainsbury | Main Street | |
95111 | ZITHROMAX 500MG TABLETS | 02:41.0 | Sainsbury | Main Street | |
95109 | CLINDAMYCIN 150MG CAPSULES | 53:49.0 | Sainsbury | Main Street | |
95109 | TAGERA FORTE (SECNIDAZOLE) 1G TABLETS | 53:49.0 | Sainsbury | Main Street | |
95111 | CELIB (CELECOXIB) 200MG CAPSULES | 02:41.0 | Sainsbury | Main Street | |
95111 | HISTALIX COUGH SYRUP 100ML | 02:41.0 | Sainsbury | Main Street | |
95111 | FLOMIST (FLUTICASONE DIPROPIONATE) 50MCG/INHALATION NASAL SPRAY | 02:41.0 | Sainsbury | Main Street | |
95111 | CETIRIZINE 10MG TABLETS | 02:41.0 | Sainsbury | Main Street | |
95113 | ENHANCIN (CO-AMOXICLAV) 625MG TABLETS | 11:49.0 | Sainsbury | Main Street | |
95113 | DICLOFENAC SODIUM 50MG TABLETS | 11:49.0 | Sainsbury | Main Street | |
95113 | HISTALIX COUGH SYRUP 100ML | 11:49.0 | Sainsbury | Main Street | |
95121 | TENORIC (ATENOLOL 50MG; CHLORTHALIDONE 12.5MG) TABLETS | 26:40.0 | Sainsbury | Main Street | |
95121 | LISINOPRIL 10MG TABLETS | 26:40.0 | Sainsbury | Main Street | |
95121 | ACLOTAS (ACECLOFENAC) 100MG TABLETS | 26:40.0 | Sainsbury | Main Street | |
95122 | TENORIC (ATENOLOL 50MG; CHLORTHALIDONE 12.5MG) TABLETS | 34:17.0 | Sainsbury | Main Street | |
95122 | LISINOPRIL 10MG TABLETS | 34:17.0 | Sainsbury | Main Street |
I am calculating the distinct count of the Script Number as folows:
Total Dispensed Rx = DISTINCTCOUNT(PharmaAuditGW[Script Number]). This gives the following result:
However the above measure does not work with "Analyse" "Explain the Increase/Decrease" in Power BI and gives the results "No Insights Found"
I want to translate this calculation into the table and have a calculated column as follows:
The above result is way above the first figure of "191K". This is so because of the wrong calculated column which is counting all "Script Number" in the table.
How can i accomplish this through a calculated column or some other approach in order to make "Analyse" "Explain the Increase/Decrease" in Power BI work.
Regards,
Chris
8 Replies
What increase? What is the dimension/axis that you show your data by? over time? across locations?
Hi Ibendin
Below is the visual depicting the increase/decrease of "Total Dispensed Rx" over time i.e. Month:
Regards,
Chris
Chitemerere If your expected result is an Area chart like above, it seems your original measure Total Dispensed Rx = DISTINCTCOUNT(PharmaAuditGW[Script Number]) will work well in it. Put the measure as value and month as axis.
Many thanks for the response, with that, the analyze functionality does not bring any results with that measure
Chitemerere Is your date column in the Date or Date/Time type? I don't understand your Date Dispensed column. It cannot be converted to Date type. You can add a regular date column like YYYYMMDD or DD/MM/YYYY which can be detected or converted as a date column. Then extract Month data into a new column and use the month column as axis.
If it still doesn't work, can you share a screenshot or sample pbix file (removing sensitive information) that will let us know what the visual looks like now?
The Date Dispensed is Date/Time type. I have created another calculated column of date type and used this to generate a Calendar table from where i use the month in the X-axis and the prescriptions dispensed in the Y-axis
