Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello all, I'm struggling with calculating an average of values, but needing it to ignore the very first value of each day as it is always zero.
I'm averaging a total "Patient_Turnover_Time" field which list whole number values in minutes for each surgery performed. This is years’ worth of data; however, the first surgery of each day is always zero since theirs no previous surgery is preformed, so I need to ignore the first surgery of each day ("Scheduled_Start" Format example: 11/9/2018 9:00:00 AM) in this average.
I have this average in a card visual. but the user could update the dashboard with filters.
I was playing around with this, but I can't seem to complete it:
Average Turnover = CALCULATE(AVERAGE([PATIENT_TURNOVER_TIME]), ALLEXCEPT(Earliest(
Any help or suggestions would be appreciated.
Terry
Solved! Go to Solution.
@tbobolz Maybe:
Average Measure =
VAR __Table = 'Table'
VAR __IgnoreTable = SELECTCOLUMNS(SUMMARIZE('Table',[Date],"__Min",MIN([Scheduled_Start])),"__Scheduled_Start",[__Min])
VAR __AverageTable = FILTER(__Table,NOT([Scheduled_Start] IN __IgnoreTable))
RETURN
AVERAGEX(__AverageTable,[Patient_Turnover_Time])
@tbobolz Maybe:
Average Measure =
VAR __Table = 'Table'
VAR __IgnoreTable = SELECTCOLUMNS(SUMMARIZE('Table',[Date],"__Min",MIN([Scheduled_Start])),"__Scheduled_Start",[__Min])
VAR __AverageTable = FILTER(__Table,NOT([Scheduled_Start] IN __IgnoreTable))
RETURN
AVERAGEX(__AverageTable,[Patient_Turnover_Time])
Thankyou very much, you are a genius!
Below is my formula with inserted fields; however, how would I add the condition to ingore that first scheduled surgery as you have written it, but only if it is equal to zero?
@tbobolz Try:
Average Turnover =
VAR __Table = 'OR Turnover'
VAR __IgnoreTable = SELECTCOLUMNS(SUMMARIZE(FILTER('OR Turnover',[Patient_Turnover_Time]=0),[SCHEDULED_START].[Date],"__Min",MIN([Scheduled_Start])),"__[Scheduled_Start]",[__Min])
VAR __AverageTable = FILTER(__Table,NOT([Scheduled_Start] IN __IgnoreTable))
RETURN
AVERAGEX(__AverageTable,[Patient_Turnover_Time])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |