Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
user_name_
Regular 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_
Regular Visitor

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!

 

 

 

 

 

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

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.