Forum Discussion

GudduKumar's avatar
GudduKumar
Frequent Visitor
3 years ago

Create DAX code to Calculate average time on given data set

I have a table called "Employee", It has following header.

User , Employee ID, Day, Logged Hours, Productive Hours, Manager, Senior Manager, Director

 

It has more than 6 months data and user count is more than 5K.

I want to calculate weekly/Monthly Average Productive/Logged hours for each Manager, Director etc.

Condition is that user can work for maximum seven days however in calculation their work day will be consider 5.

 

e.g. one user worked Mon to Thu then worked on sat and sun so average time should be sum of all from Mon to Sun divided by 5

 

Could you please help me to write the dax code. Thanks

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    Hi,

    I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.

    Please check the below picture and the attached pbix file.

    I hope the below can provide some ideas on how to create a solution for your datamodel.

     

     

     

    • GudduKumar's avatar
      GudduKumar
      Frequent Visitor
      3 years ago

      this is not how I'm looking for.

       

      I have attached the data and expected result, could you please help me on that way.

       

      user can work for 5 days or more in a week but in average calculation denominator should be 5

  • GudduKumar's avatar
    GudduKumar
    Frequent Visitor
    3 years ago
     

    Thanks Jihwan,

     

    However this is not something I'm looking for.  

    I have very simple ask. I have data as per below

    UserDaySr. ManagerEmployee IDManager Logged HoursProductive Hours
    Aamir10 Jul 2023Ramesh108398Dinesh2.640.59
    Aamir11 Jul 2023Ramesh108398Dinesh12.74.69
    Aamir12 Jul 2023Ramesh108398Dinesh8.554.14
    Aamir13 Jul 2023Ramesh108398Dinesh4.893.76
    Aamir14 Jul 2023Ramesh108398Dinesh15.249.04
    Aamir15 Jul 2023Ramesh108398Dinesh10.368.6
    Aamir16 Jul 2023Ramesh108398Dinesh9.828.51
    Aamir17 Jul 2023Ramesh108398Dinesh11.964.33
    Adithya10 Jul 2023Aman108399Rahul10.915.38
    Adithya11 Jul 2023Aman108399Rahul7.653.82
    Adithya12 Jul 2023Aman108399Rahul10.826.78
    Adithya13 Jul 2023Aman108399Rahul8.83.97
    Adithya14 Jul 2023Aman108399Rahul3.182.35
    Adithya15 Jul 2023Aman108399Rahul1.751.52
    Adithya16 Jul 2023Aman108399Rahul0.130.09
    Adithya17 Jul 2023Aman108399Rahul5.942

     

    I have data for more than 5k Employees,

     

    My Simple request is that I want to see weekly/ Monthly Average Productive Hours For Each Manager/Sr Manager, only condition while calculating average is that Work day would be 5, even if employee has worked for 7 days.

     

    e.g. Mon: 6, Tue: 7, wed: 5, Thu:8, Sat:9, Sun:10

     

    Avg. Hrs= Sum(6+7+5+8+9+10)/5

     

    I need this in DAX code so that I can plot it for everyone.

    Thanks