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 Reference||Company||Business||Brand Division||Region||Department||Date Joined||Date Left|
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 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.
In other words, how should I build my DAX function to make it work?
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.
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:
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:
3) To count numbers of dismissed for each day and sum for the period:
4) To count number of dismissed rolling year:
5) To count Turnover rolling year:
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?
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.
Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.
Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!
We had a great 2022 with a ton of feature releases to help you drive a data culture.