Forum Discussion

RNZALR's avatar
RNZALR
Helper I
3 years ago
Solved

Player Retention Rates

Hello,   I’m looking for some dax that will calculate retention rates for me. I’m building a report for a sports body where the season starts on 1 November each year. I have a table with player reg...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    Thank you for your message.

    I am not sure how your desired outcome of a visualization looks like, but please check the below picture and the attached pbix file. I tried to create it by using my sample data.

     

     

     

     

    All measures are in the attached pbix file.

     

RNZALR's avatar
RNZALR
Helper I
3 years ago

This looks great!  My follow up question is how do I get it to count the different outputs each season?  I want to be able to sum the "New", "Re-Committed" and "Return" totals by season and then be able to divide each one by the total registered players for that season in order to get a percentage of players returning, re-committing and joining for the first time.  I can't quite seem to work out how to do that from your code.  Any advice would be excellent.

 

This is the amended dax I have used in my model:

 

Player status measure: =
VAR _prioryear =
    MAX ( 'Date Table'[Year] ) - 1
VAR _eachyearcondition =
    COUNTROWS ( 'Player Registrations' )
VAR _prioryearcondition =
    CALCULATE ( COUNTROWS ( 'Player Registrations' ), 'Date Table'[Year] = _prioryear )
VAR _previousyearscondition =
    CALCULATE ( COUNTROWS ( 'Player Registrations' ), 'Date Table'[Year] < _prioryear )
RETURN
    IF (
        HASONEVALUE ( 'Player Master'[Person ID] ) && HASONEVALUE ( 'Date Table'[Year] ),
        SWITCH (
            TRUE (),
            _prioryearcondition = 0
                && _previousyearscondition = 0
                && _eachyearcondition = 1, "New",
            _prioryearcondition = 1
                && _eachyearcondition = 1, "Re-committed",
            _prioryearcondition = 0
                && _previousyearscondition >= 1
                && _eachyearcondition = 1, "Return",
            "Not registered"
        )
    )
 
Thanks again 😀
Jihwan_Kim's avatar
Jihwan_Kim
Super User
3 years ago

Hi,

Thank you for your message.

I am not sure how your desired outcome of a visualization looks like, but please check the below picture and the attached pbix file. I tried to create it by using my sample data.

 

 

 

 

All measures are in the attached pbix file.

 

  • RNZALR's avatar
    RNZALR
    Helper I
    3 years ago

    Thank you!  This is really helpful and I think it will give me exactly what I am looking for.  I may come back with a few other points to clarify, but so far it is proving to be exactly what I am looking for and I am very grateful!  😀

  • cwillems's avatar
    cwillems
    New Member
    3 years ago

    Hi Jihwan_Kim,

     

    I tried using your PBIX file to recreate as I am trying to solve a similar problem for a sports club. See the table I got using your dax measure:

     

     

    For your reference, this is my datamodel:

     

    Here is some of the data I am working with (some fields are calculated columns):

     

    I would like to be able to show when a player is playing their first season, when they have left. That way I will be able to show a retention %: players staying/total players that year. I will then show this percentage per age group, coach, etc. Do you have any imput on this? 

     

    Thanks & appreciate your insights!