Forum Discussion

mvananaken's avatar
mvananaken
Helper II
9 years ago
Solved

Calculated table with SUMX aggregation

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...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    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

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
9 years ago

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