Computing Resource Utilization Per Day, Week and Month

No worries, atleast you have solution, mark it answer if you think it is resolved.

Getting closer now.  I have the report updated on my end.  

View 1: Correct

View 2:  Not Correct - I removed the Resource field and the hours Required stayed at 7.5, instead of keeping the sum total of all resources for that day....

hey polaris3028 seems like we are going in circles, the file you sent to me was Date -> Developer and the images you are sending now other way around Developer->Date, so I want to amke sure we are looking at the same thing to provide effective solution.

Getting closer now. Already updated the report on my end.

View 1: CORRECT

View 2: NOT CORRECT - I removed the Resource column, and the #Hours Required is only 7.5 instead of the sum total of the View 1.

Here's the updated report....

https://drive.google.com/open?id=1fXdjIV4nI0y8sKhJiWOkMS4ZIjtMLWmB

The goal is #Hours Required to keep the computed value regardless, if the table/matrix will be presented per Resource, or Daily, Weekly, Monthly Rollup.  Hope that clarifies....

parry2k - just to add up.  I used to compute the #hours required based on formula:

#Hours Required = DISTINCTCOUNT(Dev_OverAll[Developer])*7.5.

Let's take a specific example:  

Developer:  Bryan

Month:  November

#Hours Spent:  47.35

#Days Worked:  15 Days (Between Nov 1-27)

%Utilization = 47.35 / 112.5, which is 42.09%

This can only be achieved if I change the formula from #Hours Required = DISTINCTCOUNT(Dev_OverAll[Developer])*7.5 to:

#Hours Required = DISTINCTCOUNT(Dev_OverAll[DevDate])*7.5. 

Any suggestion?

I think you need to use calculated table to resolve it, try following steps:

Add calculated column in your table

#my Key = Dev_OverAll[Developer] & Format( Dev_OverAll[DevDate], "YYYYMMDD")

Goto modelling tab and click new table and add following expression:

Developer Date Table = SUMMARIZECOLUMNS(Dev_OverAll[#my Key], "Hours Required", MAX(Dev_OverAll[#Hours Required2]))

in data modelling tab, change  Default summarization of "Hours Required" field in this new table to SUM

you will see new table in your model, now set relationship between this new table wiht dev table on #myKey

- in value, drop "Hours Required" from this new table, and make sure aggregation is "SUM"

test it

I'm getting this error:

Would that matter since the #Hours Required field is a measure and not a column?

yes it need to be column, the file you sent me in that #Hours Required 2 is a column and that is why I used that one, seems like you are making chages at your end at the same time :(

Opps, sorry about that.  I restored the previous version and did as instructed.  After creating the relationship below:

Here's the result of the table. Is there another step to take after this?

It's not correct stil. :(

what aggegation you are using for "Hours Required" it suppose to be SUM

It's SUM... and I tried to add the Hours Required field in the table to check the result....

Is the relationship as shown above correct?  What should be the %Utilization after we set a new table?

polaris3028 not sure what you are showing here, you need to show what aggregation you used in value section when you drop "Hours Required" from this new calculated table? sorry if i'm not clear what I'm asking.

one more thing check your relationship is both direction, not single.

not sure either... but here's the aggregation in the table

check relationship please, make sure it is both direction.

YAY, can breathe now. Glad to hear it is fixed. Cheers buddy and good luck :)