Forum Discussion

ConnieMaldonado's avatar
ConnieMaldonado
Icon for Responsive Resident rankResponsive Resident
5 years ago
Solved

Calculate Service for Distinct Techs

Hello - I'm trying to calculate the average service for technicians trained by specific trainers.  My data looks like this:

 

DateTech NameTech Hire DateTech Employee NumberTrainer EE NumberTrainer NameOfficeTraining Classroom HoursTraining Ride Along HoursTotal Training Hours
1/1/2021John Doe5/15/2018103665148780Trainer 1Raleigh7 7
1/2/2021John Doe5/15/2018103665148780Trainer 1Raleigh6 6
1/3/2021John Doe5/15/2018103665148780Trainer 1Raleigh448
1/4/2021John Doe5/15/2018103665148780Trainer 1Raleigh8 8
1/1/2021Joe Smith4/3/201912345148780Trainer 1Raleigh5 5
1/2/2021Joe Smith4/3/201912345148780Trainer 1Raleigh3 3
1/3/2021Joe Smith4/3/201912345148780Trainer 1Raleigh729
1/4/2021Joe Smith4/3/201912345148780Trainer 1Raleigh5 5
1/1/2021Larry Hall10/1/2020345678556677Trainer 2Atlanta7 7
1/5/2021Larry Hall10/1/2020345678556677Trainer 2Atlanta6 6
1/7/2021Larry Hall10/1/2020345678556677Trainer 2Atlanta426
1/1/2021Joe Jones1/1/2020678903556677Trainer 2Atlanta5 5
1/5/2021Joe Jones1/1/2020678903556677Trainer 2Atlanta3 3
1/7/2021Joe Jones1/1/2020678903556677Trainer 2Atlanta145

 

I can calcuate service as follows:

 

Service Months = DIVIDE (Today() - Tech Hire Date, 30)

 

To calculate the average for each trainer (of their respective trainees), I can sum the service months and divide by the number of distinct techs.  My issue is that since I have the same technicians for for multiple dates, the service is counted multiple times.  I need to sum the service months for distinct techs.  Is there a way to do that?

 

Here's the output I want:

 

Trainer NameTrainer EE NumberNumber of Trainees (Technicians)Average length of service of technicians (trainees) in MONTHS
Trainer 1148780                                                  254
Trainer 2556677                                                  216

 

Any help would be appreciated.  Thanks!

 

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    ConnieMaldonado , You can have measure like this

    sumx(summarize(Table, Table[Tech Name],[Tech Hire Date]), datediff([Tech Hire Date], today(),DAY)/30)

    You can have

    Averagex(summarize(Table, Table[Tech Name],[Tech Hire Date]), datediff([Tech Hire Date], today(),DAY)/30)

     

    or use min of hire date

    sumx(summarize(Table, Table[Tech Name], "_TechHireDate",Min([Tech Hire Date])), datediff([_TechHireDate], today(),DAY)/30)

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    ConnieMaldonado , You can have measure like this

    sumx(summarize(Table, Table[Tech Name],[Tech Hire Date]), datediff([Tech Hire Date], today(),DAY)/30)

    You can have

    Averagex(summarize(Table, Table[Tech Name],[Tech Hire Date]), datediff([Tech Hire Date], today(),DAY)/30)

     

    or use min of hire date

    sumx(summarize(Table, Table[Tech Name], "_TechHireDate",Min([Tech Hire Date])), datediff([_TechHireDate], today(),DAY)/30)