Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Kudoed Authors