Display employee retention rate by decimal years

[rouxknee]

Hello!  I have a dataset that includes employee tenure in decimal years.  I'd like create a line graph that shows the retention rate where the X axis is years and the Y axiz is percent retention.  Essentially, it looks at:

count of employees with a tenure value >= value at X axis / total count of records. 

Values for this graph might be:

95% retention at 1 year

93% retention at 1.5 years

20% retention at 12.5 years, etc.

I imagine it will look something like this (with one line):

Since I couldn't figure out how to attach data, here's a small dummy version of the dataset:

Employee	Tenure
2180	0.9
2589	1.81
2489	1.9
2233	1.97
2323	2.15
2061	2.57
2463	4.92
2964	7.95
2481	9.07
2682	11.68
2894	12.92
2947	13.01
2919	14.4
2912	14.45
2792	31.79
2805	37.1
2782	37.51

[parry2k]

does this look right from the sample data you shared, if yes, then i will share the solution

[rouxknee]

Unfortunately, no.  At year 0, retention should be 100%.  With these data, at year 1 value should be 94%, at year 2, 76%.

Though, having written that, I may now have an idea on how to solve.

[parry2k]

i guess like this @rouxknee

[rouxknee]

Very close - the line looks correct!

The values on the line correspond to year 1, year 2, etc. rather than year 2, year 4 (as displayed).

[parry2k]

it is based on a calculated field, so i just created gap of 2 year, can be easily moved to each year. 

[rouxknee]

Great! What's the solution?

[parry2k · Accepted solution]

First add a column to create year for each employee based on tenure:

Year = var x =  FLOOR(Table1[Tenure],1) return if(x>12,12,x)

add measure for total employee count

Total Emp = CALCULATE(COUNT(Table1[Employee]), ALL(Table1))

add measure for running total 

Running Total = CALCULATE(COUNT(Table1[Employee]), Filter(ALL(Table1), Table1[Year]>=MAX(Table1[Year]))) 

add measure for retention %

Retention = DIVIDE([Running Total], [Total Emp], 0)

drop line visual wit year on x axis and retention on values.

Change retention measure format to %

Although these measures can be done in one measure, but I like to keep it seperate for simplicty and also  can be reused in other areas as well.

This is all for reference, you can change calc as you see fit.

[rouxknee]

Thank you for this, @parry2k - it worked.

I'd like to go a little deeper and maybe you can help me.  I have another field - "Veteran Status" - that has values of "Veteran" and "Not a Veteran," how do I create individual lines for each of those values? 

I tried using a slicer and using "Veteran Status" in legend, but both ways, the lines that are given just show each group within the context of the whole dataset.  That is, I need a line for each status that ignores everything in the other status.

I need to do this for a number of different fields, so anything dynamic that can will change the line by using slicers (as opposed to sets of calculated measures for each group) would be most helpful.

[parry2k]

Put that field on legend and see if works

make sure your value section has only one field.

[rouxknee]

See previous post - putting it on legend only shows the value for that group as they relate to the whole dataset - I need it to show in the context of *only* that group.

Ideally, I'd like to show the individual lines on the same graph - that is, show the curves of retention rate for veterans vs. non-veterans.

[rouxknee]

Deleted a post - understood that first item was a column, not a measure.