Group hours by week number and employee in table

[Anonymous]

Hello!

I am newer to Power BI, so I apologize if this is an easy fix. I have a table that includes payroll information (employee, ID, Hours, Date, Etc..). I've created a new WeekNumber column that captures each pay period and I've been able to get the total hours across the company by week using the formula below:

Weekly Hour Total =

CALCULATE

(SUM(TimeCardEntry[hours]),

FILTER(TimeCardEntry,TimeCardEntry[WeekNumber]=EARLIER(TimeCardEntry[WeekNumber])))

However, what I'd really like to accomplish is getting the hours grouped by employee AND by week number. I can't seem to figure out how to accomplish this. Any help would be much appreciated!

[v-eqin-msft]

Hi @Anonymous ,

Please follow @Greg_Deckler 's advice:

1.Use Sum type

2. Aplply Conditional formatting:

Then the backgrouond of sum hours >=40 will be red ,<40 will be green.

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

This would not have given me the desired result. Instead what I ended up doing was using two filters. See below:

Weekly Hour Total =

CALCULATE

(SUM(TimeCardEntry[hours]),

FILTER(TimeCardEntry,TimeCardEntry[party_name]=EARLIER(TimeCardEntry[party_name])),

FILTER(TimeCardEntry,TimeCardEntry[WeekNumber]=earlier(TimeCardEntry[WeekNumber])))

This allowed me to get the total number of hours for each user for a given week number. 

thanks for following up!

[Greg_Deckler]

@Anonymous Could you just put both the employee and week number in a matrix along with a simple sum?

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Anonymous]

@Greg_Deckler, thanks for replying! I want to be able to set a quick filter for my users to be able to see if the employee is above or below 40 hours for each given week which is why I thought I'd need a column to show that information. Below is some sample data to reference:

Employee	Date	Hours	Weeknumber	Total Weekly Hours
John Doe	10/26/2021	5	43	33
John Doe	10/26/2021	8	43	33
John Doe	10/26/2021	8	43	33
John Doe	10/25/2021	8	43	33
John Doe	10/22/2021	4	43	33
Brandon Jackson	10/25/2021	8	43	24
Brandon Jackson	10/22/2021	8	43	24
Brandon Jackson	10/22/2021	8	43	24
Brandon Jackson	10/18/2021	8	42	28
Brandon Jackson	10/18/2021	4	42	28
Brandon Jackson	10/15/2021	4	42	28
Brandon Jackson	10/14/2021	8	42	28
Brandon Jackson	10/14/2021	4	42	28

I currently have the employee, date, hours, and weeknumber in my table. I am hoping to create the Total weekly hours column which is the total hours the employee worked during each given pay period.  Please let me know if you need any additional information. 

thanks for your help!

[Greg_Deckler]

@Anonymous What about using conditional formatting? Or you could go with a complex selector: https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Complex-Selector/m-p/1116633#M534