Calculating % Hours worked for a category

Hi Anonymous

Thank you for your reply. I like the idea of using the condition to return the static value (although I can't say I completely understand the DAX :smileyhappy:)

Here are my results when I use your code:

 As before, the first table is Average Hours Worked per Week, the second is my simple % of Each Category Calculation as explained above, and the third is a table with your suggested code. The results don't seem to have djusted too much in most cases, the exception being "Person 120", who's numbers change a small amount. I would expect to see a bigger disparity between the two highlighted people, for example. The final table on the right is one containing a very simple calculation. I basically took Average Hours Worked/Week and Divided that number by 40 to get my results.

Average Hours_Worked per Week:=AVERAGEX(
	KEEPFILTERS(VALUES('Calendar'[Week Number (Format)])),
	CALCULATE([Total Hours Worked])
)

C40:=[Average Hours_Worked per Week]/40

This seems to work, as you can see the difference in the reported %. ALthough, I feel like I may be overlooking something and an issue may surface later, but for now it seems to work so I'll take it!

A couple of additional questions, is the way I am calculating % Hours Worked for each category the best way to go about it? The formula doesn't seem very flexible and I ran into some situations where I was limited, like trying to use it in some Time Intelligence Functions. For example, I will want to be able to track change MoM, YoY, etc…

Also, I am running into another issue with Calculating the Average Hours Worked. The formula above works fine when I am not filtering for any dates and looking at one year as a whole, the formula finds the average across 52 weeks in my calendar table and all is good. However, once I try to drill down into Quarters/Months, the numbers get skewed. Since I am basing my Average formula on Week Number, when i filter my calendar table it shows a total of 5 or 6 weeks. This causes the Average Hours Worked to look much smaller than they actually are. 

To get around this, I am using the following formula:

Avg Hours Worked/Week = IF(ISFILTERED('Calendar'[Month Name]),DIVIDE([Hours_Worked],4),IF(ISFILTERED('Calendar'[Quarter]),DIVIDE([Hours_Worked],12),DIVIDE([Hours_Worked],DISTINCTCOUNT('Calendar'[Week Number]))))

However , there must be a better way? This formula does not work in the Time Intelligent functions I use either. I'll also, need to track MoM, YoY, QoQ, etc.. with this measure as well.

All that put simply, I need to track Weekly, Monthly, and Yearly trends for Average Hours Worked and % by Category, and I am having difficulty.

Please let me know if it would be best to create a new post for these additional questions, I just wanted to be respectful and not spam the boards as much as possible.

Thank you for your assistance.