Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

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:

 

Capture.PNG

 

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

 

7 REPLIES 7
Anonymous
Not applicable

Updated with tables and examples.

 

Thank you!

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

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 Smiley Happy)

 

Here are my results when I use your code:

Capture2PNG.PNG

 

 

 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. 

 

Capture3.PNG

 

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. 

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

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

v-shex-msft
Community Support
Community Support

Hi @Anonymous,

 

Can you please share some sample data for test?

How to Get Your Question Answered Quickly

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

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!

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.