Forum Discussion

minapot's avatar
minapot
Frequent Visitor
8 years ago
Solved

Avg Hrs Worked

Hi! Need help please on the following calculation that I need to translate to PBI. Here's what I got in Excel.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

I need to be able to compute for the overall avg hours work per site and account using the formulas noted on the screenshot.

 

Thanks for all the help!

Need Help
  • danextian's avatar
    danextian
    8 years ago

    Hi minapot,

     

    Try this:

     

    Avg =
IF (
    HASONEVALUE ( table[emp_id] ),
    DIVIDE ( SUM ( table[total hrs worked] ), COUNT ( table[wknum] ) ),
    DIVIDE (
        DIVIDE ( COUNT ( table[wknum] ), [measure to divide] ),
        DISTINCTCOUNT ( table[emp_id] )
    )
)

    Replace the text in red with the appropriate measure since you did not mention which column to divide the count of weeks by for the average. 

3 Replies

  • Floriankx's avatar
    Floriankx
    Solution Sage
    8 years ago

    Hello,

     

    after loading your data to DataModel you can create the following measures:

    CountOfWeeks=Count(Table[wknum])
    Total_hrs=Sum(Table[total hrs worked])
    avg_hrs=[Total_hrs]/[CountOfWeeks]

     

    Best regards.

    • minapot's avatar
      minapot
      Frequent Visitor
      8 years ago

      Hi Floriankx! Thanks for your reply! I tried your suggestions but failed to get the correct output.

      For the overall avg hrs worked, I need to have the total worked hrs for the site/account and then divide that by the average number of weeks the employees for that site/account worked and then divide the answer to the distinct # of employees for that site/account.

       

      so... site/account avg hrs worked = (total hours worked per site/ave # of weeks with schedule)/distinctcount(HC)

      • danextian's avatar
        danextian
        Super User
        8 years ago

        Hi minapot,

         

        Try this:

         

        Avg =
IF (
    HASONEVALUE ( table[emp_id] ),
    DIVIDE ( SUM ( table[total hrs worked] ), COUNT ( table[wknum] ) ),
    DIVIDE (
        DIVIDE ( COUNT ( table[wknum] ), [measure to divide] ),
        DISTINCTCOUNT ( table[emp_id] )
    )
)

        Replace the text in red with the appropriate measure since you did not mention which column to divide the count of weeks by for the average.