Calculate average of DISTINCTCOUNT by week number

[ebhdlt]

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:

• Calendar: all information related to dates (day, week number, etc.)
• Presence: this table contains a column called [PatientsPresent] which itself contains a unique ID for each patient who is present on a given day.

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!

[v-xuding-msft]

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 )

[Greg_Deckler]

Seems like you should just have to do COUNTROWS then... Not entirely clear here, something is up because that should be relatively easy.