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 AdjustedXSunday july 3, 2016a Not Overhead18exempt39939XSunday july 3, 2016overheadOverhead9exempt39939XSunday july 3, 2016bNot Overhead0exempt39939XSunday july 3, 2016cNot Overhead12exempt39939 XSunday july 3, 2016a Not Overhead18exempt40940XSunday july 3, 2016overheadOverhead9exempt40940XSunday july 3, 2016bNot Overhead0exempt40940XSunday july 3, 2016cNot Overhead13exempt40940 XSunday july 3, 2016a Not Overhead18exempt542340XSunday july 3, 2016overheadOverhead9exempt542340XSunday july 3, 2016bNot Overhead0exempt542340XSunday july 3, 2016cNot Overhead13exempt542340XSunday july 3, 2016overheadOverhead14exempt542340 YSunday july 3, 2016a Not Overhead18Non - Exempt (Hourly)39939YSunday july 3, 2016overheadOverhead9Non - Exempt (Hourly)39939YSunday july 3, 2016bNot Overhead0Non - Exempt (Hourly)39939YSunday july 3, 2016cNot Overhead12Non - Exempt (Hourly)39939 YSunday july 3, 2016a Not Overhead18Non - Exempt (Hourly)40940YSunday july 3, 2016overheadOverhead9Non - Exempt (Hourly)40940YSunday july 3, 2016bNot Overhead0Non - Exempt (Hourly)40940YSunday july 3, 2016cNot Overhead13Non - Exempt (Hourly)40940 YSunday july 3, 2016a Not Overhead18Non - Exempt (Hourly)542354YSunday july 3, 2016overheadOverhead9Non - Exempt (Hourly)542354YSunday july 3, 2016bNot Overhead0Non - Exempt (Hourly)542354YSunday july 3, 2016cNot Overhead13Non - Exempt (Hourly)542354YSunday july 3, 2016overheadOverhead14Non - Exempt (Hourly)542354

TryColumn = 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?

Ranneh

Frequent Visitor

10 years ago

Solved

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.

Employee	start of week date	Assignment name	Type of assignment	hours worked	employment type	Total Hours	Total Overhead	Total Hours Adjusted
X	Sunday july 3, 2016	a	Not Overhead	18	exempt	39	9	39
X	Sunday july 3, 2016	overhead	Overhead	9	exempt	39	9	39
X	Sunday july 3, 2016	b	Not Overhead	0	exempt	39	9	39
X	Sunday july 3, 2016	c	Not Overhead	12	exempt	39	9	39

X	Sunday july 3, 2016	a	Not Overhead	18	exempt	40	9	40
X	Sunday july 3, 2016	overhead	Overhead	9	exempt	40	9	40
X	Sunday july 3, 2016	b	Not Overhead	0	exempt	40	9	40
X	Sunday july 3, 2016	c	Not Overhead	13	exempt	40	9	40

X	Sunday july 3, 2016	a	Not Overhead	18	exempt	54	23	40
X	Sunday july 3, 2016	overhead	Overhead	9	exempt	54	23	40
X	Sunday july 3, 2016	b	Not Overhead	0	exempt	54	23	40
X	Sunday july 3, 2016	c	Not Overhead	13	exempt	54	23	40
X	Sunday july 3, 2016	overhead	Overhead	14	exempt	54	23	40

Y	Sunday july 3, 2016	a	Not Overhead	18	Non - Exempt (Hourly)	39	9	39
Y	Sunday july 3, 2016	overhead	Overhead	9	Non - Exempt (Hourly)	39	9	39
Y	Sunday july 3, 2016	b	Not Overhead	0	Non - Exempt (Hourly)	39	9	39
Y	Sunday july 3, 2016	c	Not Overhead	12	Non - Exempt (Hourly)	39	9	39

Y	Sunday july 3, 2016	a	Not Overhead	18	Non - Exempt (Hourly)	40	9	40
Y	Sunday july 3, 2016	overhead	Overhead	9	Non - Exempt (Hourly)	40	9	40
Y	Sunday july 3, 2016	b	Not Overhead	0	Non - Exempt (Hourly)	40	9	40
Y	Sunday july 3, 2016	c	Not Overhead	13	Non - Exempt (Hourly)	40	9	40

Y	Sunday july 3, 2016	a	Not Overhead	18	Non - Exempt (Hourly)	54	23	54
Y	Sunday july 3, 2016	overhead	Overhead	9	Non - Exempt (Hourly)	54	23	54
Y	Sunday july 3, 2016	b	Not Overhead	0	Non - Exempt (Hourly)	54	23	54
Y	Sunday july 3, 2016	c	Not Overhead	13	Non - Exempt (Hourly)	54	23	54
Y	Sunday july 3, 2016	overhead	Overhead	14	Non - Exempt (Hourly)	54	23	54

Need Help

jahida
10 years ago
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

Ranneh

Frequent Visitor

10 years ago

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

Employee	start of week date	Assignment name	Type of assignment	hours worked	employment type	Total Hours	Total Overhead	Total Hours Adjusted
X	Sunday july 3, 2016	a	Not Overhead	18	exempt	39	9	39
X	Sunday july 3, 2016	overhead	Overhead	9	exempt	39	9	39
X	Sunday july 3, 2016	b	Not Overhead	0	exempt	39	9	39
X	Sunday july 3, 2016	c	Not Overhead	12	exempt	39	9	39

X	Sunday july 10, 2016	a	Not Overhead	18	exempt	40	9	40
X	Sunday july 10, 2016	overhead	Overhead	9	exempt	40	9	40
X	Sunday july 10, 2016	b	Not Overhead	0	exempt	40	9	40
X	Sunday july 10, 2016	c	Not Overhead	13	exempt	40	9	40

X	Sunday july 17, 2016	a	Not Overhead	18	exempt	54	23	40
X	Sunday july 17, 2016	overhead	Overhead	9	exempt	54	23	40
X	Sunday july 17, 2016	b	Not Overhead	0	exempt	54	23	40
X	Sunday july 17, 2016	c	Not Overhead	13	exempt	54	23	40
X	Sunday july 17, 2016	overhead	Overhead	14	exempt	54	23	40

Y	Sunday july 3, 2016	a	Not Overhead	18	Non - Exempt (Hourly)	39	9	39
Y	Sunday july 3, 2016	overhead	Overhead	9	Non - Exempt (Hourly)	39	9	39
Y	Sunday july 3, 2016	b	Not Overhead	0	Non - Exempt (Hourly)	39	9	39
Y	Sunday july 3, 2016	c	Not Overhead	12	Non - Exempt (Hourly)	39	9	39

Y	Sunday july 10, 2016	a	Not Overhead	18	Non - Exempt (Hourly)	40	9	40
Y	Sunday july 10, 2016	overhead	Overhead	9	Non - Exempt (Hourly)	40	9	40
Y	Sunday july 10, 2016	b	Not Overhead	0	Non - Exempt (Hourly)	40	9	40
Y	Sunday july 10, 2016	c	Not Overhead	13	Non - Exempt (Hourly)	40	9	40

Y	Sunday july 17, 2016	a	Not Overhead	18	Non - Exempt (Hourly)	54	23	54
Y	Sunday july 17, 2016	overhead	Overhead	9	Non - Exempt (Hourly)	54	23	54
Y	Sunday july 17, 2016	b	Not Overhead	0	Non - Exempt (Hourly)	54	23	54
Y	Sunday july 17, 2016	c	Not Overhead	13	Non - Exempt (Hourly)	54	23	54
Y	Sunday july 17, 2016	overhead	Overhead	14	Non - Exempt (Hourly)	54	23	54

jahida
Impactful Individual
10 years ago
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?
- Ranneh
  Frequent Visitor
  10 years ago
  Thanks jahida it works!

Forum Discussion

help needed with a measure

3 Replies

Related content

Need help creating a measure

Need help regarding fine measure formulation

Need help dax measure

Measure/Model calculation support needed

Dax measure needed