cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Calculate average by week number in a measure

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!

1 ACCEPTED SOLUTION
Helper I

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.

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

2 REPLIES 2
Helper I

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.

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

Frequent Visitor

@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``````

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors