cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
user_name_ Frequent Visitor
Frequent Visitor

Calculation Turnover by Dept

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!

 

 

 

 

 

4 REPLIES 4
user_name_ Frequent Visitor
Frequent Visitor

Turnover by Dept

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 Super Contributor
Super Contributor

Re: Turnover by Dept

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_ Frequent Visitor
Frequent Visitor

Re: Turnover by Dept

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. 

 

 

Turnover.JPG

v-huizhn-msft Super Contributor
Super Contributor

Re: Turnover by Dept

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

 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 87 members 2,857 guests
Please welcome our newest community members: