How to create a new table based on another table?

[TomLU123]

Hi Expert,

I have a data set which is the list of employees, their department and slary. The table looks like below:

Employee ID	Department	Salary
A	HR	100
B	HR	200
C	IT	100
D	Marketing	300
E	Marketing	400
F	Admin	300
G	Admin	400
H	Admin	200

I wan to create a new table (not the visualization in the canva) which summaries the headcount, total labor cost and average labor cost of each department. 

Department	Total Labor Cost	Headcount	Av. Labor Cost (Total Labor Cost/Headcount)
HR	300	2	150
IT	100	1	100
Marketing	700	2	350
Admin	900	3	300

Is it possible to use the DAX to achieve that?

Many thanks!

Best regards,

Tom

[PattemManohar · Accepted solution]

@TomLU123 Please create a New Table as below

Test270Out = SUMMARIZE(Test270Summarize,Test270Summarize[Department],"TotalLabourCost",SUM(Test270Summarize[Salary]),"HeadCount",COUNT(Test270Summarize[Employee ID]),"AvgLabourCost",AVERAGE(Test270Summarize[Salary]))