calculating %age resource allocation

[Anonymous]

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-Month	Workinghours	CAP
2020-01	154	154
2020-02	140	21

[amitchandak]

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

Appreciate your Kudos.

[Anonymous]

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]

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

[Anonymous]

Hi,

Workinghours = Working days in month * hours per day

Working days in month = manually entered per month

hours per day = 7

CAP = sum(ResourceTimephasedDataSet[Capacity])

ResourceTimephasedDataSet[Capacity] = Column in Project Online which is linked to Power BI as Data Source

Here is the output I am expecting:

Year-Month	Workinghours	CAP	%age CAP
2020-01	154	26	17%
2020-02	140	21	15%

[v-easonf-msft · Accepted solution]

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.

[Anonymous]

This works. Thanks a lot  🙂