Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I would like to calculate Employee Utilization % (SUM of Hrs within a timeframe divided by NormalWeeklyHours) with data below:
FactTable
EmployeeNumber | BusinessDate | Hrs | DimTable.NormalWeeklyHours |
123 | 9/26/2022 | 8 | 40 |
123 | 9/27/2022 | 8 | 40 |
123 | 10/3/2022 | 8 | 40 |
123 | 10/4/2022 | 8 | 40 |
123 | 10/5/2022 | 8 | 40 |
123 | 10/11/2022 | 10 | 40 |
123 | 10/12/2022 | 10 | 40 |
123 | 10/13/2022 | 10 | 40 |
234 | 10/3/2022 | 14 | 38 |
234 | 10/5/2022 | 12 | 38 |
234 | 10/6/2022 | 12 | 38 |
345 | 9/28/2022 | 10 | 20 |
345 | 10/5/2022 | 10 | 20 |
345 | 10/11/2022 | 12 | 20 |
456 | 10/13/2022 | 8 | blank |
567 | 10/11/2022 | 8 | 40 |
567 | 10/12/2022 | 8 | 40 |
DimTable
EmployeeNumber | NormalWeeklyHours |
123 | 40 |
234 | 38 |
345 | 20 |
456 | blank |
567 | 40 |
There might be some rows with no value for NormalWeeklyHours. I have Merged the tables to get all the info in ‘FactTable’, but I’m not sure if that’s helpful.
In the visual I would like to include a slider with Business Dates to allow the users to adjust the time frame so the formula has to include a calculation to adjust NormalWeeklyHours with the time period since it is hours per week or 7 days; e.g. days between MIN BusinessDate and MAX BusinessDate is n, then NormalWeeklyHours have to be prorated by n/7
Result visual should look like this:
a)
EmployeeNumber | Total Hrs | NormalWeeklyHrs | Utilization |
123 | 24 | 40 | 60% |
234 | 38 | 38 | 100% |
345 | 10 | 20 | 50% |
Example 1: Time frame n=7
b)
EmployeeNumber | Total Hrs | NormalWeeklyHrs | Utilization |
123 | 54 | 40 | 59.23% |
234 | 38 | 38 | 53.85% |
345 | 32 | 20 | 86.15% |
456 | 8 | blank | blank |
567 | 16 | 40 | 21.54% |
Example 2: Time frame n=13
Solved! Go to Solution.
Hi @TMN_help ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want.
1. Create a date dimension table and apply the date field on the slicer
2. Create two measures to get the Utilization:
Total Hrs = SUM('FactTable'[Hrs])
Utilization =
VAR _normalwhours =
CALCULATE (
MAX ( 'DimTable'[NormalWeeklyHours] ),
FILTER (
'DimTable',
'DimTable'[EmployeeNumber] = SELECTEDVALUE ( FactTable[EmployeeNumber] )
)
)
VAR _days =
COUNTROWS (
DATESBETWEEN ( 'Date'[Date], MIN ( 'Date'[Date] ), MAX ( 'Date'[Date] ) )
)
RETURN
DIVIDE ( [Total Hrs], DIVIDE ( _normalwhours * _days, 7 ) )
If the above ones can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
Hi @TMN_help ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want.
1. Create a date dimension table and apply the date field on the slicer
2. Create two measures to get the Utilization:
Total Hrs = SUM('FactTable'[Hrs])
Utilization =
VAR _normalwhours =
CALCULATE (
MAX ( 'DimTable'[NormalWeeklyHours] ),
FILTER (
'DimTable',
'DimTable'[EmployeeNumber] = SELECTEDVALUE ( FactTable[EmployeeNumber] )
)
)
VAR _days =
COUNTROWS (
DATESBETWEEN ( 'Date'[Date], MIN ( 'Date'[Date] ), MAX ( 'Date'[Date] ) )
)
RETURN
DIVIDE ( [Total Hrs], DIVIDE ( _normalwhours * _days, 7 ) )
If the above ones can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
This is what I meant, I just didn't know what should those 2 VAR look like. Thank you for your help, it worked!
Hi @TMN_help ,
Thanks for your feedback. The variable _normalwhours is used to get the normal work hours for per user from the dimension table, and the variable _days is used to get the number of days between the selected min date and selected max date on the slicer.
Best Regards
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |