Calculating % Hours worked for a category

[Anonymous]

Good afternoon everyone! First time poster here, long time lurker. Thank you for viewing my question! I am trying to build a scorecard using PowerBi.

I have a table with the following fields: Team, Date, Employee, Hours Worked, Category. There are 3 different types of Categories: A, B, C. My goal originally was to discover what % of an employees time is being allocated to each category respectively. Simple enough, I did the calculation in PowerBI and was able to get accurate numbers. However, I realized that this % was equalizing people that worked over 40 hours with people who worked less. E.G - 80% of time working on Category A for someone working 55 hours per week on average means a lot more than someone with the same time in Category A but only works 35 hours per week on average. I need some way to account for this, but am not sure how to approach it. Almost as if I could calculate their percentages of each category as a number higher than 100%, since they are working more. I hope this question makes sense, if not please let me know if there's any other information I could provide or any way to clear things up. 

Thank you for your time.

Best

--EDIT--

I have two tables, a calendar table and a data table. My data table looks like this*:

Team	Employee	Client	Task	Hours Worked	Date	Category
Team 2	Person 1	Client 1	Task 1	8.00	1/1/2018	Category A
Team 2	Person 1	Client 1	Task 2	8.00	1/5/2018	Category B
Team 2	Person 1	Client 1	Task 2	8.00	1/2/2018	Category A
Team 2	Person 1	Client 1	Task 2	8.00	1/4/2018	Category C
Team 2	Person 2	Client 1	Task 2	1.50	1/3/2018	Category C
Team 2	Person 2	Client 1	Task 2	3.00	1/8/2018	Category B
Team 2	Person 2	Client 1	Task 2	1.00	1/9/2018	Category A
Team 2	Person 2	Client 1	Task 2	2.00	1/11/2018	Category B
Team 2	Person 2	Client 1	Task 2	4.00	1/12/2018	Category B
Team 2	Person 1	Client 1	Task 2	8.00	1/10/2018	Category A
Team 2	Person 1	Client 1	Task 2	8.00	1/18/2018	Category C
Team 2	Person 2	Client 1	Task 2	8.00	1/15/2018	Category A
Team 2	Person 1	Client 1	Task 2	8.00	1/19/2018	Category A
Team 2	Person 1	Client 1	Task 2	8.00	1/16/2018	Category C
Team 2	Person 1	Client 1	Task 2	8.00	1/17/2018	Category A
Team 2	Person 1	Client 2	Task 3	1.50	1/23/2018	Category B
Team 2	Person 1	Client 2	Task 4	1.00	1/23/2018	Category A
Team 2	Person 1	Client 2	Task 5	1.00	1/22/2018	Category A

*(In my actual table there are over a hundred "Employees" and the dates are from all of 2018.)

I found the % hours worked for each category using the following formulas:

Total Hours Worked:=SUM(Data[Hours Worked])

Category Overall Total:=CALCULATE([Total Hours Worked],ALL(Data[Category]))

% Category:=[Total Hours Worked]/[Category Overall Total]

Here's an example of my results:

The table on the left shows the average hours worked/person, and the one on the right is the % of their TOTAL hours worked by category. As you can see, person 128 only works an average of 8hrs a week, but in that disparity isn't displayed in the second table, by nature of the math. Category A is most important to me, so I want people to spend most of their time there, however person 132 spending 53% of his time in Category A is infinitely more valuable as a they work 40hrs/week vs. 8. I am trying to find some way to report on this, but have not been able to come up with a solution. 

I hope this is clear, please let me know if there is more I can clear up. 

Thank you,

Mike

[Anonymous]

Updated with tables and examples.

Thank you!

[v-shex-msft]

HI @Anonymous,

Maybe you can add a condition to your measure formula to return static value(8 hour * planned working days) if records has missed some category types.

Sample formula:

% Category 2 = 
VAR planned =
    MAXX (
        SUMMARIZE (
            ALLSELECTED ( 'Sample' ),
            [Team],
            [Client],
            [Task],
            [Employee],
            "Workday Count", COUNT ( 'Sample'[Date] )
        ),
        [Workday Count]
    )
VAR currCategory =
    CALCULATE (
        COUNTROWS ( VALUES ( 'Sample'[Category] ) ),
        ALLSELECTED ( 'Sample' ),
        VALUES ( 'Sample'[Employee] )
    )
RETURN
    IF (
        currCategory = COUNTROWS ( DISTINCT ( ALLSELECTED ( 'Sample'[Category] ) ) ),
        [Total Hours Worked] / [Category Overall Total],
        [Total Hours Worked]
            / ( planned * 8 )
    )

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @v-shex-msft

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 )

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. 

[v-shex-msft]

HI @Anonymous,

>>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!

I'd like to suggest you to use dynamic workdays * daily work hour to instead, it will get wrong result when you are choose multiple weeks or try to drill calculation to days level.

>>However , there must be a better way? This formula does not work in the Time Intelligent functions I use either.

Time intelligence function not suppoert calculate with complex conditions, filters or other scenario.

Maybe you can you can consider to manually define date filter range to calculate.

Time Intelligence "The Hard Way" (TITHW)

Regards,

Xiaoxin Sheng

[Anonymous]

I'll give that a try. Thank you for all your help! 

[v-shex-msft]

Hi @Anonymous,

Can you please share some sample data for test?

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

[Anonymous]

Xiaoxin, thank you for your response. I apologize for not putting my question into the proper format. I will put together some sample data/tables and update my original post. 

Thank you!