Forum Discussion

Frequent Visitor

2 years ago

Solved

Seeking help for Utilization rate calculation and presenting it over a matrix table

I would like to calculate the utilization % of the employees and present the data over a matrix table. I have the following DAX but it doesn’t seem to tally up correctly. x_Utilization% = C...

talespin
2 years ago
hi kevinpbi

I used the sample data which you shared, there are some changes between your and mine data model. Listing everything below.

1. Data model
-There are no limited relationship, like in your datamodel.
-I have used one to many relationship and single direction. Both tables filter Timesheet table.

2. Used Matrix with Year, month, Country, Empoyee Name and Activity type.
Create these measures and put them in Values. I have verified the numbers per logic shared by you. Please test them thoroughly.

Measure1
Count Working Days =
CALCULATE( COUNT('CALENDAR'[Date]), 'CALENDAR'[IsWorkingDay])

Measure2
Total Duration hours = SUM(Timesheet[DurationInHours])

Measure3
Utilization Pct =
VAR _WorkHrsPerDay = 8
VAR _WorkingDays = [Count Working Days]
VAR _PlannedWrkHrs = (_WorkingDays*_WorkHrsPerDay)
VAR _CountEmployee = CALCULATE(COUNT(DimEmployee[EmployeeName]), REMOVEFILTERS(Timesheet[ActivityType]), DimEmployee[Status] = "Active" )
VAR _AllEmpPlannedWrkHrs = (_PlannedWrkHrs*_CountEmployee)
RETURN DIVIDE([Total Duration hours], _AllEmpPlannedWrkHrs)

Anonymous

Not applicable

2 years ago

Hi kevinpbi ,

If I understand correctly, the issue is that you couldn’t calculate the utilization of the employees. Please try the following methods and check if they can solve your problem:

1.Modify the DAX formula for utilization. Enter the following formula.

x_Utilization% = 
CALCULATE(
    SUMX(
        FILTER(
            Timesheet,
            Timesheet[-Status] = "Approved"
        ),
        Timesheet[-DurationInHours]
    ),
    ALL(DateTable), 
    'Employee Data'[-CreatedOn] <= MAX(Timesheet[-ActivityDate]) &&
    'Employee Data'[-ModifiedOn] >= MIN(Timesheet[-ActivityDate])
) / CALCULATE(
    SUMX(
        FILTER(
            DateTable,
            DateTable[IsWorkday] = 1 &&
            DateTable[Date] >= MIN('Employee Data'[-CreatedOn]) &&
            DateTable[Date] <= MAX('Employee Data'[-ModifiedOn])
        ),
        DateTable[IsWorkHours]
    ),
    ALL(Timesheet)
)

If the above ones can’t help you get it working, could you please provide the DateTable raw data(exclude sensitive data) with Text format? It would be helpful to find out the solution. I am not sure about the data inside the Date Table.