Hi all,
I'm trying to be smarter than I am and get some sort of dashboard that calculates the spot rate (# leavers month / # headcount month) and 12m rolling rate (sum leavers last 12 months/ average headcount last 12 months) of my company.
I could create a dashboard to do this the standard way with a great deal of data manipulation, formulae etc but I was wondering if I can do something more efficient, powerful and effective. I have been looking into Power Query / Power Pivot but struggling to make it happen
I have two tabs:
1. Cumulative Headcount tab. In this tab (300k+ rows) I have got a headcount snapshot of the past 2 years aprox. I have got employee details including Employee ID as well reporting month etc
2. Cumulative Leavers. In this tab (5k+ rows) I have got a list of leavers of the past 2 years aprox. I have got employee details including Employee ID (which I thought would be my unique identifier) as well leaving month etc
I just want to know if there is a way (sure there is) to make this happen using Power Query / Power Pivot. Ideally I would like to display spot rates for the past 12 months and 12m rolling rate all at once for my selection (I would like to do this by using multiple slicers - gender, tenure, business unit, division etc).
Just can't get my head around it so I wondered if anyone can help! Thanks for your help in advance!
E
Provide us with a file of sample data 15 records or so and then mock up what you want the result to look like for the records provided. It will make it easier to provide you with a workable solution.
Hi Alan,
Thanks for your reply. Please find attached, hope that helps.
The end goal would be to create a basic dashboard with multiple slicers / buttons to make a specific selection (Entity / Division / Business Unit / Gender / Tenure etc) and get the spot rates and 12 month rolling rate. I'm quite flexible with this and still thinking the best way to make it as visual as possible but that would be a good start 😉
The headcount data tab contains a snapshot of our monthly headcount. A column named "HC Month" allow us to determine the headcount for each of the months so that we can calculate spot rates and 12m rolling rate.
The leavers tab has got a very similar format. Once again, there is a leaving month allowing us to determine the leavers for each month and calculate both rates accordingly.
For example, we had 3 leavers in August 21 and the headcount for that month was 5 (please ignore dummy names / employee numbers on both tabs as they wont make any sense) therefore the spot rate would be 60% (3/5) and the spot rate would be (sum leavers between Sep 20 to Aug 21) / (avg. headcount between Sep 20 to Aug 21).
As mentioned on my first post I would be able to create this "the old way" creating multiple pivots, formulae etc but I sense the file is going to be unmanageable (HC tab has got 300k records already). I'm sure this could be more powerful and efficient using Power Query / Pivot but just struggling to understand how to start it all!
Regards
E
