Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Dear Power BI community,
I have data related to patient admissions in a healthcare facility.
I am trying to calculate the number of patients present each day (i.e. the occupancy) on average per week number (see last column in red):
Day | Week number | Occupancy | Average occupancy per week |
January 1 2020 | 1 | 115 | 141.85 |
January 2 2020 | 1 | 139 | 141.85 |
January 3 2020 | 1 | 158 | 141.85 |
January 4 2020 | 1 | 167 | 141.85 |
January 5 2020 | 1 | 164 | 141.85 |
January 6 2020 | 1 | 145 | 141.85 |
January 7 2020 | 1 | 105 | 141.85 |
January 8 2020 | 2 | 104 | 121.57 |
January 9 2020 | 2 | 120 | 121.57 |
January 10 2020 | 2 | 130 | 121.57 |
January 11 2020 | 2 | 141 | 121.57 |
January 12 2020 | 2 | 141 | 121.57 |
January 13 2020 | 2 | 125 | 121.57 |
January 14 2020 | 2 | 90 | 121.57 |
I have the following tables:
For example:
Date | PatientsPresent |
January 1 2020 | Patient_1_ID |
January 1 2020 | Patient_2_ID |
January 2 2020 | Patient_1_ID |
January 2 2020 | Patient_3_ID |
January 3 2020 | Patient_3_ID |
January 1 2020: Patients 1 & 2 are present; DISTINCTCOUNT = 2
January 2 2020: Patients 1 & 3 are present (patient 2 was discharged); DISTINCTCOUNT = 2
January 3 2020: Patient 3 is present (patient 1 was discharged); DISTINCTCOUNT = 1
I am currently using the following measure:
Measure =
VAR _WeekdaysINWeekNum = CALCULATE(COUNTROWS('Calendar'), FILTER(ALLSELECTED('Calendar'), 'Calendar'[WeekNumber] = MAX('Calendar'[WeekNumber])))
VAR _OccupancyPerWeek = CALCULATE(DISTINCTCOUNT('Presence'[PresentPatients]), FILTER(ALLSELECTED('Calendar'), 'Calendar'[WeekNumber] = MAX('Calendar'[WeekNumber])))
RETURN
_OccupancyPerWeek / _WeekdaysINWeekNum
The problem is that is provides me with the number of distinct patients per week number (divided by the number of week days).
However, I want the sum of all patients present during each day (divided by the number of week days). This means that patients who are present on more than one day will be counted more than once, which is what it should do. But when I use COUNT instead of DISTINCTCOUNT, I get incorrect numbers.
Thank you for your help!
Hi @ebhdlt ,
Have resolved this problem? If not, maybe you could try the following formula.
AvgWeeklySales =
VAR _WeekdaysINWeekNum =
CALCULATE (
COUNTROWS ( 'Calendar' ),
FILTER (
ALLSELECTED ( 'Calendar' ),
'Calendar'[WeekNumber] = MAX ( 'Calendar'[WeekNumber] )
)
)
VAR _OccupancyPerWeek =
CALCULATE ( COUNTROWS ( 'Table' ), ALLEXCEPT ( 'Calendar', 'Calendar'[Date] ) )
RETURN
DIVIDE ( _OccupancyPerWeek, _WeekdaysINWeekNum )
Seems like you should just have to do COUNTROWS then... Not entirely clear here, something is up because that should be relatively easy.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |