Calculated table with SUMX aggregation

[mvananaken]

Hello Folks,

I got a example table with (note: occurence multiple rows per employee per date is possible)

Date               Employee        hours

2016-01-01   Employee A       8

2016-01-01   Employee A       8

2016-01-01   Employee B       8

2016-01-01   Employee B       8

2016-01-01   Employee C       8

Now i want a  new calculated table with:

Date               Employee        hours

2016-01-01   Employee A       16

2016-01-01   Employee B       16

2016-01-01   Employee C       8

(background: in this table I want to made a custom column with a corrected hours of max 8 hours per date per employee), and the corrected hours are the base to make a calculed sum of (corrected) hours. 

How to achieve this?

Best regards,

Marco

[BhaveshPatel]

Hi Marco,

You can use GroupBy in the Query Editor to get the expected results.

GROUPBYEXPECTED RESULTS

OR YOU CAN CREATE A CALCULATED COLUMN WITH THE BELOW DAX SYNTEX.

TOTAL= CALCULATE(SUM[Table[Hours]),

                          FILTER(Table,

                                      Table[Employee]=EARLIER(Tabel[Employee])

                                      )

                                    )

[Aleks]

Great solution! Thank you very much!

[mvananaken]

Hello @BhaveshPatel,

Thanks for help! I really appreciate it.

The first edit query method is not possible in my case, because I want to use a related custom column  in DAX to group by, who won't exist in edit query. But in other cases: this is the best solution i guess.

The second DAX option works, but I need specific a sum of hours per employee per date. How to achieve this? (new calculated table or an other possible way) 

Best regards,

Marco van Aken

[v-yulgu-msft · Accepted solution]

Hi @mvananaken,

Based on my understanding, the "corrected hours of max 8 hours" means the max hour value is 8 even if one employee's working hours in one day is larger than 8 hours, right?

If so, you can add a calculated column in original table to generate the corrected hours. DAX formula can be:

Column = IF('SUMX'[Hours]>8,8,'SUMX'[Hours])

Then, new a calculate table to get the sum of hours per employee per date.

SUMX2 =
SUMMARIZE (
    'SUMX',
    'SUMX'[Date],
    'SUMX'[Employee],
    "Total", SUM ( 'SUMX'[Column] )
)

If you have any question, please feel free to ask.

Best regards,
Yuliana Gu

[mvananaken]

Thanks a lot @v-yulgu-msft!!