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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
v-yiruan-msft
Community Support
Community Support

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

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

View solution in original post

3 REPLIES 3
v-yiruan-msft
Community Support
Community Support

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

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

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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