Forum Discussion

waffle85's avatar
waffle85
Regular Visitor
6 years ago
Solved

Counting Full Time Employees By Week

Hi all. I have a fair amount of experience with Tableau, but just switched to Power BI. I have a table that includes Employee, Hours, and Date Worked. I'm trying to calculate what percent of full time an employee is (40 hours worked in a week = 1.0; 20 hours = 0.5 ; 60 hours = 1.0) and then create a line chart that sums those values to show the total full time employee count by week.

 

Any help is greatly appreciated.

 

Thanks,

 

Mike 

Need Help
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    The best way to do this would be to make a Date table (https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/) with a Week column, with a 1:Many relationship with your original table (called Hours, for example).  You can then write a measure like this to go in a visual with your week column.

     

    Total Employees =
VAR summarytable =
    SUMMARIZE (
        Hours,
        Hours[Employee],
        'Date'[Week],
        "@HoursWorked", SUM ( Hours[Hours] )
    )
RETURN
    SUMX ( summarytable, IF ( [@HoursWorked] < 40, [@HoursWorked] / 40, 1 ) )

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

3 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    6 years ago

    The best way to do this would be to make a Date table (https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/) with a Week column, with a 1:Many relationship with your original table (called Hours, for example).  You can then write a measure like this to go in a visual with your week column.

     

    Total Employees =
VAR summarytable =
    SUMMARIZE (
        Hours,
        Hours[Employee],
        'Date'[Week],
        "@HoursWorked", SUM ( Hours[Hours] )
    )
RETURN
    SUMX ( summarytable, IF ( [@HoursWorked] < 40, [@HoursWorked] / 40, 1 ) )

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    6 years ago

    Hi ,  waffle85 

    If you have a date type column,just need to create a weeknum column as below:

    Weeknum = WEEKNUM('Table'[Date Worked])

    Then create calculate column as below:

    1percent of full time by week = 
var a = CALCULATE(SUM('Table'[Hour]),ALLEXCEPT('Table','Table'[Weeknum],'Table'[Employee]))/40
return IF(a>1,1,a)

     

     

    pbix attached

     

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.