Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

GroupBY with a constant value

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 , 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)

2 Replies

  • 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)

  • Anonymous's avatar
    Anonymous
    Not applicable

    Thanks, creating a new column worked!