This was quite difficult to understand the data structure.
As shown above, the Department is not aligned between Jan 1st and March 31st, Then, total count of Employee ID is not correct for Department whichever department field you use..
While, I have not checked detail, but guess the "Band" is same.
The point is that
Because the department is different from three data set, you can not use "department" across three data point to calculate number of employees.
What I tried here is that I calculated(counted) employee ID per department @ respective Data set.
Then, connect the data with linking "Department" as a key.
I think you can restructure how to get the data from other system, but I did with using given data set.
Anyway, I hope you can understand what should be done thru this concept.
Duplicate (x2) the data source and rename "3 month" , " Jan 1" ,a and "Mar 31".
Link the data as below table
Count Employee ID at respective data point
Then on primary data source add create calculated field.
[HC as on 1st Jan]
[Exits in 3 months (Tableau-Assignment) Jan1].[Count Emp ID Jan1]
[HC as on 31st March]
[Exits in 3 months (Tableau-Assignment) Mar31].[Countd EMP ID Mar 31]
Below two can stay same.
([HC as on 1st Jan]+[HC as on 31st March])/2
[Number of Exits in 3 months]/[AVG HC]
Then I got what I believe correct data.
There are still disconnection from Excel validation number, but I'm afraid excel formula is wrong like below.
Also, PC Smart Device Count of Employee ID is something different.
Tableau shows 11,but excel shows 4.
Dashboard _ 9.1.twbx 140.9 KB