Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Simple_one
Helper I
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

Simple_one_0-1709545158855.png

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

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION
talespin
Solution Sage
Solution Sage

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
        )
)
 
talespin_0-1709548367217.png

 

View solution in original post

3 REPLIES 3
talespin
Solution Sage
Solution Sage

hi @Simple_one 

 

You're welcome.

talespin
Solution Sage
Solution Sage

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
        )
)
 
talespin_0-1709548367217.png

 

Dear @talespin ,

Thank you so much ! This is so excellent

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.