Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
ebhdlt
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:

 

image_2020.04.17.png

 

 

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

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:

1.PNG

 

Best wishes.

 

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

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

View solution in original post

2 REPLIES 2

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:

1.PNG

 

Best wishes.

 

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

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

@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

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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