cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ranneh Frequent Visitor
Frequent Visitor

help needed with a measure

Hi All,

 

I am looking for a measure in power BI desktop that will allow me create a sum of various rows based on specific criteria.

The criteria being the name of employee and the start of week date (first and second columns in the sample below)

i need to calculate how many hours are being worked by the same individual on the same week and have that info displayed into a new column (column 7: total hours)

The reason why i need this displayed in a new column is because i will then use that input in column 7 to create a new formula in column 9.

i am good to go with the latter part of the exercise but my problem is that i am not being able to come up with a way to give me the sum of hours per week per employee.

your help is appreciated.

Thank you.

 

Employeestart of week dateAssignment nameType of assignmenthours workedemployment typeTotal Hours Total OverheadTotal Hours Adjusted
XSunday july 3, 2016Not Overhead18exempt39939
XSunday july 3, 2016overheadOverhead9exempt39939
XSunday july 3, 2016bNot Overhead0exempt39939
XSunday july 3, 2016cNot Overhead12exempt39939
         
XSunday july 3, 2016Not Overhead18exempt40940
XSunday july 3, 2016overheadOverhead9exempt40940
XSunday july 3, 2016bNot Overhead0exempt40940
XSunday july 3, 2016cNot Overhead13exempt40940
         
XSunday july 3, 2016Not Overhead18exempt542340
XSunday july 3, 2016overheadOverhead9exempt542340
XSunday july 3, 2016bNot Overhead0exempt542340
XSunday july 3, 2016cNot Overhead13exempt542340
XSunday july 3, 2016overheadOverhead14exempt542340
         
YSunday july 3, 2016Not Overhead18Non - Exempt (Hourly)39939
YSunday july 3, 2016overheadOverhead9Non - Exempt (Hourly)39939
YSunday july 3, 2016bNot Overhead0Non - Exempt (Hourly)39939
YSunday july 3, 2016cNot Overhead12Non - Exempt (Hourly)39939
         
YSunday july 3, 2016Not Overhead18Non - Exempt (Hourly)40940
YSunday july 3, 2016overheadOverhead9Non - Exempt (Hourly)40940
YSunday july 3, 2016bNot Overhead0Non - Exempt (Hourly)40940
YSunday july 3, 2016cNot Overhead13Non - Exempt (Hourly)40940
         
YSunday july 3, 2016Not Overhead18Non - Exempt (Hourly)542354
YSunday july 3, 2016overheadOverhead9Non - Exempt (Hourly)542354
YSunday july 3, 2016bNot Overhead0Non - Exempt (Hourly)542354
YSunday july 3, 2016cNot Overhead13Non - Exempt (Hourly)542354
YSunday july 3, 2016overheadOverhead14Non - Exempt (Hourly)542354

 

1 ACCEPTED SOLUTION

Accepted Solutions
jahida Established Member
Established Member

Re: help needed with a measure

Try

Column = CALCULATE(SUM(Table[hours worked]), ALLEXCEPT(Table, Table[Employee], Table[start of week date]))

 

EDIT: I'm a bit confused, do you want a measure or a column?

3 REPLIES 3
Ranneh Frequent Visitor
Frequent Visitor

Re: help needed with a measure

had a typo with the dates, here is the right sample

 

Employeestart of week dateAssignment nameType of assignmenthours workedemployment typeTotal Hours Total OverheadTotal Hours Adjusted
XSunday july 3, 2016Not Overhead18exempt39939
XSunday july 3, 2016overheadOverhead9exempt39939
XSunday july 3, 2016bNot Overhead0exempt39939
XSunday july 3, 2016cNot Overhead12exempt39939
         
XSunday july 10, 2016Not Overhead18exempt40940
XSunday july 10, 2016overheadOverhead9exempt40940
XSunday july 10, 2016bNot Overhead0exempt40940
XSunday july 10, 2016cNot Overhead13exempt40940
         
XSunday july 17, 2016Not Overhead18exempt542340
XSunday july 17, 2016overheadOverhead9exempt542340
XSunday july 17, 2016bNot Overhead0exempt542340
XSunday july 17, 2016cNot Overhead13exempt542340
XSunday july 17, 2016overheadOverhead14exempt542340
         
YSunday july 3, 2016Not Overhead18Non - Exempt (Hourly)39939
YSunday july 3, 2016overheadOverhead9Non - Exempt (Hourly)39939
YSunday july 3, 2016bNot Overhead0Non - Exempt (Hourly)39939
YSunday july 3, 2016cNot Overhead12Non - Exempt (Hourly)39939
         
YSunday july 10, 2016Not Overhead18Non - Exempt (Hourly)40940
YSunday july 10, 2016overheadOverhead9Non - Exempt (Hourly)40940
YSunday july 10, 2016bNot Overhead0Non - Exempt (Hourly)40940
YSunday july 10, 2016cNot Overhead13Non - Exempt (Hourly)40940
         
YSunday july 17, 2016Not Overhead18Non - Exempt (Hourly)542354
YSunday july 17, 2016overheadOverhead9Non - Exempt (Hourly)542354
YSunday july 17, 2016bNot Overhead0Non - Exempt (Hourly)542354
YSunday july 17, 2016cNot Overhead13Non - Exempt (Hourly)542354
YSunday july 17, 2016overheadOverhead14Non - Exempt (Hourly)542354
jahida Established Member
Established Member

Re: help needed with a measure

Try

Column = CALCULATE(SUM(Table[hours worked]), ALLEXCEPT(Table, Table[Employee], Table[start of week date]))

 

EDIT: I'm a bit confused, do you want a measure or a column?

Highlighted
Ranneh Frequent Visitor
Frequent Visitor

Re: help needed with a measure

Thanks @jahida it works!