Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
TMN_help
Frequent Visitor

How to calculate employee utilization % that adjusts with the timeframe adjusted by slicer

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%

TMN_help_2-1666643396776.png

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%

TMN_help_1-1666643298035.png

Example 2: Time frame n=13

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

yingyinr_3-1666680100972.png

yingyinr_4-1666680112891.png

 

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 ) )

yingyinr_2-1666680027533.png

 

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

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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

yingyinr_3-1666680100972.png

yingyinr_4-1666680112891.png

 

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 ) )

yingyinr_2-1666680027533.png

 

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!

Anonymous
Not applicable

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.