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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
mg09
New Member

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

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.
2 ACCEPTED SOLUTIONS
v-jialongy-msft
Community Support
Community Support

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:

vjialongymsft_0-1735870396629.png

 

 

 

 

 

 

Best Regards,

Jayleny

 

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

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)

 

vjialongymsft_0-1736753158250.png

 

 

 

Result:

vjialongymsft_2-1736753302792.png

 

 

 

 

 

 

Best Regards,

Jayleny

 

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

5 REPLIES 5
v-jialongy-msft
Community Support
Community Support

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:

vjialongymsft_0-1735870396629.png

 

 

 

 

 

 

Best Regards,

Jayleny

 

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

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. 

exampleEmp.jpg

 

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)

 

vjialongymsft_0-1736753158250.png

 

 

 

Result:

vjialongymsft_2-1736753302792.png

 

 

 

 

 

 

Best Regards,

Jayleny

 

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

Thank you, that solution worked.

bhanu_gautam
Super User
Super User

@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:

 

 




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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