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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Kiranbc
Helper I
Helper I

Help required diving individual hours by total billable hours.

Hi All,

Refer below screen shot : I want to divide hours/Amount : 156  with billable hours(measure) 160.50 and expecting client utilization has .971 and similarly 4/160.50 and expecting .028, but right now it is dividing row by row giving 1. Please help me dividing billable hours total (160.5) by individual hours. 

 

Kiranbc_0-1664200822069.png

 

1 ACCEPTED SOLUTION

Can you test your variable, to see what it returns.

In the formula after RETURN replace the divide function and all its argument, by the name of the variable you want to test.

Check if CurrentEmployeeH and TotalHours are giving expected result.

RETURN
CurrentEmployeeH 

then same with Totalhours. Let us know

 

View solution in original post

7 REPLIES 7
AilleryO
Memorable Member
Memorable Member

Hi,

To get your usage percentage, you can do :

% by Employee = 
VAR CurrEmployee =  [Employee]
VAR CurrentEmployeeH = CALCULATE( SUM([Hours Amount]) , [Employee] = CurrEmployee , ALL ( [Clients] ) )
VAR TotalHours = CALCULATE( SUM([Hours Amount]) , ALL (  ) )
RETURN
DIVIDE ( CurrentEmployeeH , TotalHours )

 

Hope it helps

Thanks. I am unable to refer the Employee column in the sheet name utilization. Please refer below screenshot.

 

Kiranbc_0-1664202437675.png

 

Make the change just on the 1st VAR, not on the line 3

I am getting zero.

Kiranbc_0-1664204040067.png

% by Employee =
VAR CurrEmployee = SELECTEDVALUE(Utilization[Employee])
VAR CurrentEmployeeH = CALCULATE( SUM(Utilization[Hours/Amount]) , (Utilization[Employee]) = CurrEmployee, ALL (Utilization[client names] ) )
VAR TotalHours = CALCULATE( SUM(Utilization[Hours/Amount]) , ALL ( ) )
RETURN
DIVIDE ( CurrentEmployeeH , TotalHours )


Can you test your variable, to see what it returns.

In the formula after RETURN replace the divide function and all its argument, by the name of the variable you want to test.

Check if CurrentEmployeeH and TotalHours are giving expected result.

RETURN
CurrentEmployeeH 

then same with Totalhours. Let us know

 

I am getting below error now.

Kiranbc_1-1664203368623.png

 

Hi,

Sorry, try SELECTEDVALUE([Employee]) instead of [Employee] for the VAR CurrEmployee, ie :

VAR CurrEmployee=SELECTEDVALUE([Employee])

Let us know

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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