Need help to calculate the average of an average

[Simple_one]

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

I have a power BI in this link : https://drive.google.com/file/d/13J44Tg-b_ixRSpYRcEp2noUD_tKTbLhl/view?usp=drive_link 

Please help me for calculating the  average of an average ! Thank you very much

[talespin]

hi @Simple_one 

You're welcome.

[talespin · Accepted solution]

hi @Simple_one 

Please try this

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

        )

)

[Simple_one]

Dear @talespin ,

Thank you so much ! This is so excellent