Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 ri...
Need Help
Show and Tell
Tips and Tricks
  • AilleryO's avatar
    AilleryO
    4 years ago

    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

     

AilleryO's avatar
AilleryO
Icon for Memorable Member rankMemorable Member
4 years ago

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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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

 

 

  • AilleryO's avatar
    AilleryO
    Icon for Memorable Member rankMemorable Member
    4 years ago

    Hi,

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

    VAR CurrEmployee=SELECTEDVALUE([Employee])

    Let us know

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    I am getting below error now.

     

  • AilleryO's avatar
    AilleryO
    Icon for Memorable Member rankMemorable Member
    4 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      I am getting zero.

      % 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 )


      • AilleryO's avatar
        AilleryO
        Icon for Memorable Member rankMemorable Member
        4 years ago

        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