Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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)
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.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thanks! That worked great.
User | Count |
---|---|
107 | |
88 | |
81 | |
76 | |
73 |
User | Count |
---|---|
112 | |
104 | |
96 | |
74 | |
66 |