Forum Discussion
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 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
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
5 Replies
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])
)
)
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
Great solution! Thank you very much!
