Forum Discussion
Calculated table with SUMX aggregation
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
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
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
Thanks a lot v-yulgu-msft!!