cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EZgrafton
Advocate I
Advocate I

Calculate Employees Turnover

I need to calculate the employees turnover and allow the users to drill down our five level of hierarchy (so, basically down to department level).

 

First question, can I do that on Power BI or should I do it on Power Pivot first?

 

Second, my data set looks like this:

Person ReferenceCompanyBusinessBrand DivisionRegionDepartmentDate JoinedDate Left
206100120230384104580907-Feb-197231-Dec-2011
207100120230384102581125-May-196221-Jul-2012
210100120330394103581303-Feb-197531-Dec-2013
212100120330364110581401-Jan-200906-Apr-2013
213100020430314079573101-May-1996 

 

I need to be able to calculate the employees turnover (count of prior 12 months leavers divided by the monthly average headcount based on the past 12 months) by Company, Business, Brand Division, Region, and Department.

 

Any help is greatly appreciated! Please keep in mind that I am new to Power BI and DAX.

 

Thank you.

5 REPLIES 5
Eric_Zhang
Microsoft
Microsoft

@EZgrafton

 

Acutally a preview feature:inline hierarchy has been instroduced in March 2016's release. Please check the below link

Use inline hierarchy labels in Power BI Desktop

 

If you have any question, feel free to let me know.

Thank you Eric. The inline hierarchy labels are very useful. However, I still cannot figure out how to use them to calculate and visulize the turnover through the five levels of hierarchy. Cat Mad

 

In other words, how should I build my DAX function to make it work?

 

Thank you

Hello.

In my dataset i use this base mesures:

1)  To count headcount per day and then average from it. For one day the result will be the sum, but for any wider segments it will be average.

Average Headcount :=
CALCULATE (
    AVERAGEX (
        ADDCOLUMNS (
            VALUES ( 'Calendar'[Date] );
            "HC"SUMX (
                FILTER (
                    'Base';
                    'Base'[Start date of the period] <= 'Calendar'[Date]
                        && 'Base'[End date of the period] >= 'Calendar'[Date]
                );
                'Base'[Headcount]
            )
        );
        [HC]
    );
    ALL ( 'Base'[Company attitude to dismissal]; 'Base'[Reason for dismissal] )
)

I use ALL ( 'Base'[Company attitude to dismissal]; 'Base'[Reason for dismissal] 

to calculate the average number in any sections, but without taking into account the details on the fields 'Base'[Company attitude to dismissal] (regret, non regret) and 'Base'[Reason for dismissal].

2) To count the average for the rolling year

 a) in Calendar table add column to count day number from the calendar start date:

SequentialDayNumber

=
COUNTROWS (
    FILTER (
        ALL ( 'Calendar' );
        'Calendar'[Date] <= EARLIER ( 'Calendar'[Date] )
            && NOT (
                MONTH ( 'Calendar'[Date] ) = 2
                && DAY ( 'Calendar'[Date] ) = 29
            )
    )
)
 

In my calendar there are all dates from the beginning of the year the minimum date of hiring to the end date of the analyzed year. In practice, for each frame move I received a new line with the date of the beginning of the event, the date of completion of the event, the date of hiring (not tied to the dates of the event, only to the current period of work in the company) and the date of dismissal (if not dismissed, date = 01.01 .2099).

 

b) to count rolling year:

 

Average Headcount rolling year :=
CALCULATE (
    [Average Headcount];
    FILTER (
        ALL ( 'Calendar' );
        'Calendar'[SequentialDayNumber]
            > MAX ( 'Calendar'[SequentialDayNumber] ) - 365
            && 'Calendar'[SequentialDayNumber] <= MAX ( 'Calendar'[SequentialDayNumber] )
    )
)

3) To count numbers of dismissed for each day and sum for the period:

Number of Dismissed :=
SUMX (
    ADDCOLUMNS (
        VALUES ( 'Calendar'[Date] );
        "Dismissed"SUMX (
            FILTER (
                'Base';
                'Base'[Start date of the period] <= 'Calendar'[Date]
                    && 'Base'[End date of the period] >= 'Calendar'[Date]
                    && 'Base'[Date of dismissal] = 'Calendar'[Date]
            );
            'Base'[Headcount]
        )
    );
    [Dismissed]
)

4) To count number of dismissed rolling year:

Number of Dismissed rolling year :=
CALCULATE (
    [Number of Dismissed];
    FILTER (
        ALL ( 'Calendar' );
        'Calendar'[SequentialDayNumber]
            > MAX ( 'Calendar'[SequentialDayNumber] ) - 365
            && 'Calendar'[SequentialDayNumber] <= MAX ( 'Calendar'[SequentialDayNumber] )
    )
)

5) To count Turnover rolling year:

Staff turnover rolling year :=
IFERROR (
    [Number of Dismissed rolling year] / [Average Headcount rolling year];
    0
)
friedrice
Advocate I
Advocate I

Hey I'm working on the exact same thing. From the sample of data you provided, it looks like our database is setup very similar. 

 

In general, I'm creating measurements and/or columns for metrics where needed. I haven't spent enough time yet on turnover rate to have it completely figured out yet. Once I do I'll share or if you already have figured it out, can you let me know how you did it?

 

Side note, is there an advantage to running your data through Power Pivot first?

Sure will! 

 

On the Power Pivot first, I am not sure. I watched a lot of videos on modeling and many of them seem to go through Power Pivot, but I don't know if it is necessary or if you can achieve the same results going straight on Power BI. I am trying to find out the most efficient way of building models. Hopefully someone will clarify this for me/us.

 

Cheers!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors