Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

calculating %age resource allocation

Hi,

 

I am creating a report to calculate %age allocation of each resource per month. It varies per month because there are different working hours each month. I am trying to calculate CAP by Workinghours to derive the %age. CAP is a measure while Workinghours is a column and PowerBI is not letting me do that. Can anyone help me with that. 

 

Here is the data set:

 

Year-MonthWorkinghoursCAP
2020-01154154
2020-0214021

 

Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    6 years ago

    Hi , Anonymous 

    Try measures as below:

    %age CAP = 
sumx(
    'Table',
    [CAP]/CALCULATE(MAX('Table'[Workinghours]))
)

    Pbix attached

     

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

6 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    The information you have provided is not making the problem clear to me. Can you please explain with an example.

    Appreciate your Kudos.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      We have capacity of each resource in Project Online i.e. CAP. For each resource, I have entered that how many hours each resource is available per month.  I need to convert the capacity for each resource to a %age. For this I need to divide the capacity by number of working hours per month. 

       

      Lets say I have a capacity of 26 hours in for Month of January and there are 154 working hours in the month, so my %age capacity will 26/154*100

       

      When I try to divide capacity by working hours, I get an error - "A single value for column"Workinghours" in table "Hourspermonth" cannot be determined. This can happen when a measure formula referes to a column taht contains many values without specifiying an aggregation such as min, max, count, or sum"

       

      Hope this helps. 

      • v-easonf-msft's avatar
        v-easonf-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi ， Anonymous 

        If I understand your requirement correctly that you want to   divide  "CAP" by  "Workinghours "
         "CAP" is a measure , "Workinghours " is a calculate column ,right ?

        If it is ,please kindly show me the dax formula  about "CAP" and "WorkingHours" .

         Sharing  PBIX/some sample data and expected result to us  will make it easier to solve your problem.

         

        BTY, The January CAP in the table  is 26 not 154, right?

         

        Best Regards,
        Community Support Team _ Eason