DAX Measure to calculate % of time for those over 70%

[mg09]

I'm looking for assistance in creating a measure.  I have a dataset of timecard details by employee.  I have a DAX measure where I am identifying employees who have used a specific code for less than 70% of their time recorded.  That measure is below.

COUNTROWS(

FILTER(

VALUES(

    'Emp Example'[Employee])

,[JZU0 Hours]/[Hours]  < .70

    )

)

I have another measure where I'm looking at the detail by employee in a matrix visual. The measure for that is below.

Employees % by COA =

DIVIDE(

    [Hours],

    CALCULATE(

        [Hours],

        REMOVEFILTERS('COA'[DESCR],COA[Sort])

    )

    )

What I now need and cannot figure out, for just those employees with a percent usage of less than 70%, I need to see the details.

In the linked .pbix, the 1st page is the details I currently have, the second page is where I need the same matrix visual but just those with a utilization less than 70%.  This needs to be dynamic; when someone selects a different time period, the calculation changes like the first measure I posted.

Below is a link to the .pbix file
https://1drv.ms/u/c/fe1c9cd49f475606/EYqXUzo2_dZLtjDQ0aUcnOoBUEMlB7WbctLXCOiKiBzUVg?e=UjFF0C 

 Thank you.

[Anonymous · Accepted solution]

Hi @mg09 

You can use the following measure to determine if Employees Utilization is less than 0.7.

IF Employees Utilization < 70% = --Number of employees who've used the utilization COA less than 70% of their time recorded

VAR _Emp = SELECTEDVALUE('Emp Example'[Employee])
VAR _Hours = SUMX(FILTER(ALL('Emp Example'),'Emp Example'[Employee] = _Emp),'Emp Example'[Hours])
VAR _JZU0_Hours = SUMX(FILTER(ALL('Emp Example'),'Emp Example'[Employee] = _Emp && 'Emp Example'[COA] = "JZU0"),'Emp Example'[Hours])
RETURN
IF(DIVIDE(_JZU0_Hours,_Hours)<0.7,1,0)

Measure 2 = CALCULATE(DISTINCTCOUNT('Emp Example'[Employee]),FILTER(ALL('Emp Example'),[IF Employees Utilization < 70%] = 1))

Result:

Best Regards,

Jayleny

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

[Anonymous · Accepted solution]

Hi @mg09 

You can use the following measure to determine if Employees Utilization is less than 0.7.

IF Employees Utilization < 70% = --Number of employees who've used the utilization COA less than 70% of their time recorded

VAR _Emp = SELECTEDVALUE('Emp Example'[Employee])
VAR _Hours = SUMX(FILTER(ALL('Emp Example'),'Emp Example'[Employee] = _Emp),'Emp Example'[Hours])
VAR _JZU0_Hours = SUMX(FILTER(ALL('Emp Example'),'Emp Example'[Employee] = _Emp && 'Emp Example'[COA] = "JZU0"),'Emp Example'[Hours])
RETURN
IF(DIVIDE(_JZU0_Hours,_Hours)<0.7,1,0)

Measure 2 = CALCULATE(DISTINCTCOUNT('Emp Example'[Employee]),FILTER(ALL('Emp Example'),[IF Employees Utilization < 70%] = 1))

Result:

Best Regards,

Jayleny

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

[mg09]

I already have the correct measure calculation for the employees who's time is less than 70%.  I'm looking at it from the perspective of if all their hours they've worked week over week is less than 70%, let me know who that is.  That's why I'm using the values function.

What I then need is the details for JUST those people.  And what I mean by details is the example on the Employee Details by COA page for JUST those 3 people.  It's a matrix visual with their names and week ending date in the rows and the COA description in the columns.

Pink highlighted I already have, I'm trying to get to the yellow highlighted. 

 

[Anonymous · Accepted solution]

Hi @mg09 

All you need to do is put the following measure into the filter.

IF Employees Utilization < 70% = --Number of employees who've used the utilization COA less than 70% of their time recorded

VAR _Emp = SELECTEDVALUE('Emp Example'[Employee])
VAR _Hours = SUMX(FILTER(ALL('Emp Example'),'Emp Example'[Employee] = _Emp),'Emp Example'[Hours])
VAR _JZU0_Hours = SUMX(FILTER(ALL('Emp Example'),'Emp Example'[Employee] = _Emp && 'Emp Example'[COA] = "JZU0"),'Emp Example'[Hours])
RETURN
IF(DIVIDE(_JZU0_Hours,_Hours)<0.7,1,0)

Result:

Best Regards,

Jayleny

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

[mg09]

Thank you, that solution worked.

[bhanu_gautam]

@mg09 , First create a measure

Create a measure to calculate the utilization percentage for each employee:

Employee Utilization % =
DIVIDE(
[JZU0 Hours],
[Hours]
)

Create a measure to identify employees with utilization less than 70%:

DAX
Employees with Utilization < 70% =
CALCULATE(
COUNTROWS('Emp Example'),
FILTER(
VALUES('Emp Example'[Employee]),
[Employee Utilization %] < 0.70
)
)

Create a measure to filter the matrix visual based on the utilization condition:

DAX
Filtered Employees % by COA =
IF(
[Employee Utilization %] < 0.70,
[Employees % by COA],
BLANK()
)

Use the Filtered Employees % by COA measure in your matrix visual: