topic Need help to calculate the average of an average in DAX Commands and Tips

Need help to calculate the average of an average

Simple_one — Mon, 04 Mar 2024 09:50:49 GMT

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

Re: Need help to calculate the average of an average

talespin — Mon, 04 Mar 2024 10:33:18 GMT

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

)

Re: Need help to calculate the average of an average

Simple_one — Mon, 04 Mar 2024 10:52:18 GMT

Dear talespin ,

Thank you so much ! This is so excellent

Re: Need help to calculate the average of an average

talespin — Mon, 04 Mar 2024 11:47:16 GMT

hi Simple_one

You're welcome.