cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Need help to calculate the average of an average

Hello everyone,

I have a dax for calculating average of hour of teacher by contract by month as below :

Average Hour :=
VAR no_teacher_full = 'List Class Schedule Teacher'[# Teacher Full Time]
VAR no_teacher_part = 'List Class Schedule Teacher'[# Teacher Part Time]
VAR teacher_contract= SELECTEDVALUE('List Class Schedule Teacher'[TeacherContractType])
VAR total_hour_fulltime= CALCULATE(SUMX(VALUES('List Class Schedule Teacher'[Date Key]),'List Class Schedule Teacher'[Total Teaching Hour]),'List Class Schedule Teacher'[TeacherContractType] ="Full Time")
VAR total_hour_partime = CALCULATE(SUMX(VALUES('List Class Schedule Teacher'[Date Key]),'List Class Schedule Teacher'[Total Teaching Hour]),'List Class Schedule Teacher'[TeacherContractType] ="Part Time")
VAR result =
SWITCH(True,
teacher_contract = "Full Time",DIVIDE( total_hour_fulltime,no_teacher_full),
teacher_contract = "Part Time",DIVIDE(total_hour_partime,no_teacher_part),
BLANK()

)
Return result

But I want the column Total is average of all of months  , not is the sum of all this average in Power BI

1 ACCEPTED SOLUTION
Solution Sage

Simplified the measure. Result on the bottom right table.

Avg Hour =
VAR teacher_contract= SELECTEDVALUE('List Class Schedule Teacher'[TeacherContractType])
VAR no_teacher = CALCULATE(DISTINCTCOUNT('List Class Schedule Teacher'[TeacherCode]), REMOVEFILTERS('List Class Schedule Teacher'[TeacherContractType]), 'List Class Schedule Teacher'[TeacherContractType]= teacher_contract)
VAR total_hour = [Total Teaching Hour]
VAR result = DIVIDE( total_hour,no_teacher)

Return IF( HASONEFILTER('Calendar'[Month Name]), result,
AVERAGEX(
VALUES('Calendar'[Month Name]),
VAR sub_no_teacher = CALCULATE(DISTINCTCOUNT('List Class Schedule Teacher'[TeacherCode]), REMOVEFILTERS('List Class Schedule Teacher'[TeacherContractType]), 'List Class Schedule Teacher'[TeacherContractType]= teacher_contract)
VAR sub_total_hour = [Total Teaching Hour]
VAR sub_result = DIVIDE( sub_total_hour,sub_no_teacher)
RETURN sub_result
)
)

3 REPLIES 3
Solution Sage

You're welcome.

Solution Sage

Simplified the measure. Result on the bottom right table.

Avg Hour =
VAR teacher_contract= SELECTEDVALUE('List Class Schedule Teacher'[TeacherContractType])
VAR no_teacher = CALCULATE(DISTINCTCOUNT('List Class Schedule Teacher'[TeacherCode]), REMOVEFILTERS('List Class Schedule Teacher'[TeacherContractType]), 'List Class Schedule Teacher'[TeacherContractType]= teacher_contract)
VAR total_hour = [Total Teaching Hour]
VAR result = DIVIDE( total_hour,no_teacher)

Return IF( HASONEFILTER('Calendar'[Month Name]), result,
AVERAGEX(
VALUES('Calendar'[Month Name]),
VAR sub_no_teacher = CALCULATE(DISTINCTCOUNT('List Class Schedule Teacher'[TeacherCode]), REMOVEFILTERS('List Class Schedule Teacher'[TeacherContractType]), 'List Class Schedule Teacher'[TeacherContractType]= teacher_contract)
VAR sub_total_hour = [Total Teaching Hour]
VAR sub_result = DIVIDE( sub_total_hour,sub_no_teacher)
RETURN sub_result
)
)

Helper I

Dear @talespin ,

Thank you so much ! This is so excellent