Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Calculating % Hours worked for a category

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*:

 

TeamEmployeeClientTaskHours WorkedDateCategory
Team 2Person 1Client 1Task 18.001/1/2018Category A
Team 2Person 1Client 1Task 28.001/5/2018Category B
Team 2Person 1Client 1Task 28.001/2/2018Category A
Team 2Person 1Client 1Task 28.001/4/2018Category C
Team 2Person 2Client 1Task 21.501/3/2018Category C
Team 2Person 2Client 1Task 23.001/8/2018Category B
Team 2Person 2Client 1Task 21.001/9/2018Category A
Team 2Person 2Client 1Task 22.001/11/2018Category B
Team 2Person 2Client 1Task 24.001/12/2018Category B
Team 2Person 1Client 1Task 28.001/10/2018Category A
Team 2Person 1Client 1Task 28.001/18/2018Category C
Team 2Person 2Client 1Task 28.001/15/2018Category A
Team 2Person 1Client 1Task 28.001/19/2018Category A
Team 2Person 1Client 1Task 28.001/16/2018Category C
Team 2Person 1Client 1Task 28.001/17/2018Category A
Team 2Person 1Client 2Task 31.501/23/2018Category B
Team 2Person 1Client 2Task 41.001/23/2018Category A
Team 2Person 1Client 2Task 51.001/22/2018Category 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

 

Need Help

7 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Updated with tables and examples.

     

    Thank you!

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

        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.