cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
minapot Frequent Visitor
Frequent Visitor

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.

 

Capture.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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!

1 ACCEPTED SOLUTION

Accepted Solutions
danextian New Contributor
New Contributor

Re: Avg Hrs Worked

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. 

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

View solution in original post

3 REPLIES 3
Floriankx Established Member
Established Member

Re: Avg Hrs Worked

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 Frequent Visitor
Frequent Visitor

Re: Avg Hrs Worked

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 New Contributor
New Contributor

Re: Avg Hrs Worked

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. 

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 45 members 846 guests
Please welcome our newest community members: