Calculation Turnover by Dept

[user_name_]

Hello, 

I am trying to calculate turnover by week by deparment and am having difficulty.

I have 4 different Tables - 2016 HC , Hires, Terms, 2017 HC. All tables have Depts. 

The 2016 HC table has the 2016 begining HC by employee, by dept (my starting point) - I then have the Hires & Terms tables, which have the Hires & Terms for both 2016 and 2017, by employee, date, and dept. 

what would be the best way to calculate a turnover by week by dept with these tables?

Thanks!

[user_name_]

Spoiler

Hello, 

I am trying to calculate turnover by week by deparment and am having difficulty.

I have 4 different Tables - 2016 HC , Hires, Terms, 2017 HC. All tables have Depts. 

The 2016 HC table has the 2016 begining HC by employee, by dept (my starting point) - I then have the Hires & Terms tables, which have the Hires & Terms for both 2016 and 2017, by employee, date, and dept. 

what would be the best way to calculate a turnover by week by dept with these tables?

Thanks!

[v-huizhn-msft]

Hi @user_name_,

It's hard to post the detailed solution without your sample table. You'd better share some sample table and list the expected result.

Based on my understanding, the turnove value must locate in one of your table, you can create relationship between your tables. To calculate turnover by week by deparment, you need to group by week or department, you can create calculated column using ALLEXCEPT like the formula.

result=CALCULATE(SUM(Table[turnover]),ALLEXCEPT(Table,Table[week]))

You can also create a new table using SUMMARIZE, you can refer to this thread.

Best Regards,
Angelia

[user_name_]

Thanks for the reply - I appreciate yoru help!

I have attached a screenshot of the tables I am working with. 

I have a 2016 Starting HC - and seperate table for Terms & Hires.  All tables have depts numbers and effective dates, which have been connected by a 'Depts' table and 'Time' Table.  All information is on an employee detail level (employee ID)

I would like to come up with a turnover dashboard by week, that can be drilled down by dept.  In order to do this, I need to grab the '2016 HC' number, then add the weekly hires and subtract the terms by week, by dept, and then calculate the turnover by week and also a running total.  I would also need to see 2016 turnover VS 2017 turnover. 

[v-huizhn-msft]

Hi @user_name_,

From your screenshot, you can create [Hires] and [Terms] measure using the formulas.

Hires=SUM(Hires[Employee number])
Terms=SUM(Terms[Employee number])

Then you can select week as rows, the [Hires] and [Terms] measure as values, create another measure using formula:  [Hires]-[Terms]

I think in yout Time table, there is 2016 and 2017 date, right? If it is, you get 2016 turnover by adding a filter like: filter(Time,Time[year]=2016).

turnover2016=CALCULATE([Hires]-[Terms],Filter(Time, Time[Year]=2016))

In addition, how to use 2016HC and 2017HC?

Best Regards,
Angelia