Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I want to display the turnover rate for 2017 and 2018 using two lines on one chart. I have a cumulative % for 2017, but it is about half of the total that it should be. What I am using to calculate this is two measures, one for total employee count during the month and the other for terminations for each month.
**Here is the measure for terminations each month. (if the end date takes place during the month minus a few exceptions)**
2017 terminations = calculate(countrows('masterall turnover'),
USERELATIONSHIP(DateTable1[Date],'masterall turnover'[end date]),
year('masterall turnover'[end date])=2017,
'masterall turnover'[termination type]<>"transfer" ,
'masterall turnover'[termination type]<>"Voluntary (Leave of Absence)",
'masterall turnover'[title]<> "non-billable" ,
'masterall turnover'[title]<> "bench"
)
I am not sure if this is the best way to do this. The reason that I have to isolate 2017 here is that I am presenting this with 2018 using the same x-axis which is just month name.
The measure I am using for employee count is :
YTD Count Employees Z =
VAR endOfPeriod =MAX ( 'DateTable1'[Date] )
VAR startOfPeriod = MIN( 'DateTable1'[Date] )
RETURN
CALCULATE (
COUNTROWS ( 'MasterAll Turnover'),
'MasterAll Turnover'[Start Date] <= endOfPeriod,
'MasterAll turnover'[End Date] >= startOfPeriod ,
'MasterAll Turnover'[title]<>"non-billable",
masterall[title]<>"bench")
To get the percentage, I am dividing the termination measure by employee count, however it is not working right. See the chart below. The "Term" Count is correct and total is correct. The employee count is correct by month, but the total at the bottom is wrong. That's the count without any filter by month. The last column "% 2017" is correct by month, but the sum total is not right. I am thinking that the Employee count sum is what is causing the % 2017 to be too low. It should be about 37%.
I have a date table named 'datetable1' that is tied to 'master turnover' table. The master turnover table is formatted as follows:
employee | start date | end date | ......*there are more columns but none relevant to this question
jim 1/1/2016 12/4/2017
@joshcomputer1,
Would you please share dummy data of your tables and post expected result here?
Regards,
Lydia
https://www.dropbox.com/s/dzpg08ap10x7hp8/Book1.xlsx?dl=0
THis file has a table named Employee that is set up the same as in the dataset. Untimately, I want to chart the agent count for 2017 and 2018 similar to what you see below (using Monthshort as the axis). If we can get this right, then I think we can divide agents termed by the agent count.
@joshcomputer1,
What does your DateTable1 table look like in Power BI Desktop?
Regards,
Lydia
DateTable looks like the below picture.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |