Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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:
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!
Solved! Go to 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!
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!
@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
User | Count |
---|---|
120 | |
72 | |
69 | |
57 | |
50 |
User | Count |
---|---|
167 | |
82 | |
68 | |
65 | |
53 |