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.
Check out the News & Announcements to learn more.
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.
Microsoft received the highest score of any vendor in both the strategy and current offering categories.
DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.