Percent distinct values year over year

[KMEAGHER]

Hello, 

I am trying to create a measure to calculate teacher turnover within the table below. Each teacher has a unique research ID, and the file contains 9 years of data. I essentially want to calculate the percent of Research ID's that were retained from one year to the next, but am stumped on how to do it. Any ideas on a calculation? Thanks!

[maruthisp · Accepted solution]

Hi @KMEAGHER,
Please find the attached pbix file, that will show the TratainedTeachers and Retention% values as per the sample data which I considered.

Percent distinct values year over year.pbix

Please let me know if you have further questions.

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X

[KMEAGHER]

thank you @maruthisp that worked!

[bhanu_gautam]

@KMEAGHER You can create a measure that calculates the percentage of Research IDs retained from one year to the next. 

Create a Calculated Column for Year: Year = YEAR([School Year])

Create a Measure for Retained Teachers:

RetainedTeachers =
VAR CurrentYear = MAX('Table'[Year])
RETURN
CALCULATE(
COUNTROWS('Table'),
FILTER(
'Table',
'Table'[Year] = CurrentYear &&
'Table'[Research Id] IN
CALCULATETABLE(
VALUES('Table'[Research Id]),
'Table'[Year] = CurrentYear - 1
)
)
)

Create a Measure for Total Teachers in Previous Year:

TotalTeachersPreviousYear =
VAR CurrentYear = MAX('Table'[Year])
RETURN
CALCULATE(
COUNTROWS('Table'),
'Table'[Year] = CurrentYear - 1
)

Create the Turnover Rate Measure:

TurnoverRate =
DIVIDE(
[RetainedTeachers],
[TotalTeachersPreviousYear],
0
)

Use a line chart or any other suitable visualization to display the turnover rate over the years.

[KMEAGHER]

thank you @bhanu_gautam for the suggestion. The TotalTeachersPreviousYear measure worked great, however the RetainedTeachers measure is not working. It doesn't show an error message, however nothing appears when I add it to a visual.