cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Chitemerere
Responsive Resident
Responsive Resident

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:

 

Total Rx Dispensed One.PNG

 

 

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:

 

Total Rx Dispensed =
CALCULATE (
DISTINCTCOUNT ( PharmaAuditGW[Script Number]),
ALLEXCEPT(PharmaAuditGW, PharmaAuditGW[Script Number])
)
 
I then try and have a measure to recalcuate "Total Dispensed Rx" as:
 
Total Rx Dispensed = PharmaAuditGW[Total Rx Dispensed].  This gives the following figure:
 
Total Rx Dispensed Two.PNG

 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 8
Chitemerere
Responsive Resident
Responsive Resident

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

@Chitemerere Is there a relationship created on the date columns between Calendar table and PharmaAuditGW table? And the cross filter direction?

Herewith image of the relationship below:

Rx Dispensed.PNG

 

Regards,

Chris

lbendlin
Super User III
Super User III

What increase? What is the dimension/axis that you show your data by?  over time? across locations?

Chitemerere
Responsive Resident
Responsive Resident

Hi Ibendin

Below is the visual depicting the increase/decrease of "Total Dispensed Rx" over time i.e. Month:

 

Rx Dispensed Analysis.PNG

 

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. 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors