GroupBY with a constant value

[Anonymous]

I have a timesheet export that contains columns with the date, acitivity and hours worked:

I wrote the following DAX GroupBy formula to group hours per day, which gives the desired result of total hours per date:

DateHours =

GROUPBY(

Sheet1,

Sheet1[Date],

"SumHours", SUMX(CURRENTGROUP(), Sheet1[Hours])

)

I want to add a line to this that calculates the amount of overtime based on an 8 hours day:
DateHours =

GROUPBY(

Sheet1,

Sheet1[Date],

"SumHours", SUMX(CURRENTGROUP(), Sheet1[Hours]),

"Overtime", SUMX(CURRENTGROUP(), Sheet1[Hours])-8)

)

This however gives me an error:

"Function 'GROUPBY' scalar expressions have to be Aggregation functions over CurrentGroup(). The expression of each Aggregation has to be either a constant or directly reference the columns in CurrentGroup()."

My question is how do I add a SUMX plus constant value and calculation in this calulated table to get the overtime hours worked per date?

[Anonymous]

Thanks, creating a new column worked!

[amitchandak · Accepted solution]

@Anonymous , You can create a new column

Overtime = Sheet1[Hours])-8

or new measure and use that in visual

"Overtime", SUMX(values(Sheet[Date]), Sheet1[Hours])-8)