Forum Discussion
Helper IHelp measures from different related tables
Hey Community, I'm having problems calculating the rotation ratio (been at it for over a week now .__.
This are my relationships:
Ceses = Leaves in company
Ingresos = Hires in the company
Centro de Costos = Clients
Trabajadores Activos = Active workers (not by dates, only updated for 17th sept)
I created a Date Table and related leaves and hires. Also I created a Clients table because I couldn't link leaves and hires, it was a many to many relationship.
So I wish to find the employee rotation ratio, filtered by client, month or year. The employee rotation ratio is calculated by dividing the amount of leaves by the amount of active workers, depending if I'm filtering client, month or year. I can't gather data of active workers per day. I can only get the active workers per client for the 17th of september so I figured I could work my way backwards and forwards by adding hires and substracting leaves of each day, since I am able to get that data. For instance if I have 1000 active workers on 17th sept and on the 16th I have 10 hires and 5 leaves, that would mean we had 1000 -10 +5 = 995 actve workers on the 16th.
I have created measures for hires and leaves:
Leaves = COUNT('Ceses OLA'[TRABAJADOR]) --- This measure was created in the Ceses (leaves) Table
Hires = COUNT('Ingresos OLA'[TRABAJADOR]) --- This measure was created in the Ingresis (hires) Table
** "TRABAJADOR" has the id for each employee.
This works when I filter it by month, year or client.
Then I created the measure Hires - leaves:
Hires - leaves = COUNT('Ingresos OLA'[TRABAJADOR]) - COUNT('Ceses OLA'[TRABAJADOR]) --- This measure was created in the Centros de Costos (Clients) Table and it also works when I filter the report by client, year or month. This is how it looks:
I need to find the amount of active workers for each day and this has to be able to be filtered by client, year or month. I have a table with the active workers and have created this measure:
Active workers = COUNT('Trabajadores activos OLA'[TRABAJADOR]) --- This measure was created in the Trabajadores activos (Active workers) Table
But this information is only for the 17th of september. This would have to be updated daily, It should look like this (I know that on th 17th we have 956 active workers because I have the measure "Active Workers".
| Year | Month | Day | Hires | Leaves | Hires - leaves | Trabajadores activos |
| 2018 | Setiembre | 1 | 12 | 1 | 11 | 930 |
| 2018 | Setiembre | 2 | 930 | |||
| 2018 | Setiembre | 3 | 7 | 3 | 4 | 934 |
| 2018 | Setiembre | 4 | 3 | 3 | 937 | |
| 2018 | Setiembre | 5 | 7 | 1 | 6 | 943 |
| 2018 | Setiembre | 6 | 3 | 1 | 2 | 945 |
| 2018 | Setiembre | 7 | 1 | 4 | -3 | 942 |
| 2018 | Setiembre | 8 | 1 | 4 | -3 | 939 |
| 2018 | Setiembre | 9 | 2 | -2 | 937 | |
| 2018 | Setiembre | 10 | 3 | 3 | 940 | |
| 2018 | Setiembre | 11 | 9 | 1 | 8 | 948 |
| 2018 | Setiembre | 12 | 3 | 2 | 1 | 949 |
| 2018 | Setiembre | 13 | 3 | 3 | 952 | |
| 2018 | Setiembre | 14 | 2 | -2 | 950 | |
| 2018 | Setiembre | 15 | 1 | 1 | 0 | 950 |
| 2018 | Setiembre | 16 | 950 | |||
| 2018 | Setiembre | 17 | 8 | 2 | 6 | 956 |
| 2018 | Setiembre | 18 | 5 | 5 | 961 | |
| 2018 | Setiembre | 19 | 4 | 2 | 2 | 963 |
| 2018 | Setiembre | 20 | 3 | -3 | 960 | |
| 2018 | Setiembre | 21 | 1 | 6 | -5 | 955 |
And it should also be able to be filtered by client, month and year.
I'm struggling with this report for over a week pleeeeease I would really appreacite some help on this.
Thanks in advance
5 Replies
v-juanli-msftCommunity Support
Hi carlosflores
Please try to use COUNT function in Calculate function, like CALCULATE(COUNT[Column],ALLSELECTED[Column1])
I am testing with your scenario and would give feedback later.
Best Regards
Maggie
- v-juanli-msft
Hi carlosflores
I want to confirm with you what is your expected result.
For example, "Hires" and "Leaves" are total sum of the whole month, "actives" is the total active workers for the whole 2018/9, then according to information above, we could get the last column/measure which shows total active workers for each month.
Is my understanding right?
Could you send me a privacy email with your example dataset?
It has mutiple tables and i need to know more about your data.
Best Reagrds
Maggie
carlosfloresv-juanli-msft thanks for you help,
"Hires" and "Leaves" are total sum of each day, and can be filtered by month or year or client.
"Actives" is the total of workers on september 17, that's when I collected the data
Your last column "actives2" is actually correct.
Lastly to finally calculate the rotation ratio, I would have to divide leaves/ actives2, and this should be able to be filtered by month, year or client.
Alsi, I sent you a private message with the dataset.
Thank you so much!
- v-juanli-msft
Hi carlosflores
my idea to your problem is :
1. for 2017/9, Trabajadores Activos for 2017/9/17 is known, then calculate the Trabajadores Activos for each day in 2017/9 based on the Trabajadores Activos for 2017/9/17 and Hires - leaves in each day.
2.after step1, i will get total Trabajadores Activos for 2017/9, then use this and Hires - leaves in each month of 2017, i will get Trabajadores Activos for each month of 2017.
As i am going on my national holiday in the following week , i have little time implementing my idea in your pbix so that i have stuck in it.
I make a test pbix to calculate Trabajadores Activos based on each month.
could you have a look at it, i hope that can provide some ideas for you.
If it still can't help you, i would continue to work on this problem when i come back, if it is urgent, you could post another thread in the community tp get help from others.
Best Regards
Maggie
- carlosflores
Hello v-juanli-msft, first of all I wanted to ley you know how much I appreciate your help on this matter.
Your test pbix works! that's what I'd like to do with my own data. The thing is, I would need to know the Trabajadores Activos por each day as well as each month so I can make reports showing the evolution of this metric over time. Also they should be able to be filtered by Client (on the left).
Just to clarify, with the Trabajadores Activos per day, then I would calculate the employee rotation by dividing the average Trabajadores Activos of the last 4 weeks by the amount of leaves of the past 4 weeks.
I'll wait for you to come back. I really need some help on this matter, I've been struggling with for so long, I almost gave up with it.
Thanks again and happy Holidays!