Avg Hrs Worked

[minapot]

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!

[Floriankx]

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]

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 · Accepted solution]

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.