Calculate average by week number in a measure

[ebhdlt]

Hello,

I have data related to patient admissions in a healthcare facility.

I am currently displaying the number of patients occupying the facility per day of the month in a bar chart as so:

My goal is to display the average occupation number per week number as a line in the bar chart (see orange line). The above image is an example of what I wish to achieve, created in Excel.

Important: I must achieve this with a measure, and not by introducing calculated columns. I am working with large datasets that are stored on a server which I am connected with through SSAS, so I cannot easily modify the data structure.

I have the following tables at my disposal:

• "Calendar" table, which includes the week number related to each date
• "Presence" table, which has a column containing a unique code for each patient that is present on a given day. Based on this column, I have a measure [Occupancy] that performs a DISTINCTCOUNT in order to determine the occupancy on a given day.

I have tried the following approaches which do not work:

ATTEMPT 1

This measure simply returns the actual occupancy number for each day of the month:

Measure =
VAR _Occupancy = [Occupancy]
VAR _WeekNum = CALCULATE(MAX(Calendar[WeekNumber]))

RETURN
AVERAGEX(SUMMARIZE(Presence, Presence[FK_Date_ID], "WeekNumber", _WeekNum), _Occupancy)

ATTEMPT 2

Again, this measure simply returns the actual occupancy number for each day of the month:

Measure = 
AVERAGEX(VALUES(Calendar[WeekNumber]), [Occupancy])

ATTEMPT 3

This attempt produces an error, although the syntax appears to be correct:

Measure = 
VAR _AvgWeeklyOccupancy = GROUPBY(Presence, Presence[FK_Date_ID], "AverageWeeklyOccupancy", AVERAGEX(CURRENTGROUP(), Presence[Occupancy]))

RETURN
MAXX(_AvgWeeklyOccupancy, AverageWeeklyOccupancy)

Thank you very much for your help!

[Motasem_Yakhola · Accepted solution]

Hello @ebhdlt

Since i don't have the raw data that you have, i prepared measure for sales data hope that will help you.

AvgWeeklySales =
Var WeekdaysINWeekNum = CALCULATE(COUNTROWS('Calendar'),FILTER(ALLSELECTED('Calendar'),'Calendar'[weeknum]=max('Calendar'[weeknum])))
Return
CALCULATE(sum(Sales[Amount])/WeekdaysINWeekNum,FILTER(ALLSELECTED('Calendar'),'Calendar'[weeknum]=max('Calendar'[weeknum])))

The result of applying this measure will be as below:

Best wishes.

-------------------------------------------------------

Did I answer your question? Mark my post as a solution!

[ebhdlt]

@Motasem_Yakhola Thank you so much, this is the solution! Greatly appreciated!!

I had to slightly tweak the formula because in my case, [Occupancy] is already a measure and not a column, therefore I cannot use it in the SUM function.

Here is my updated 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