Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

New Team vs Retained team

Hi All,

 

Can someone please help me with this query?

I am working on a dashboard but stuck in the stage where i am unable to find the count of Teams which are New and Retained.

Example Data set is like this : Application_ID, Session time and duration, Season.
Each unique application ID can be considered as a Team/Centre

I want to know
1. The Distinct count of Application ID under each season.
2. The distinct count of application IDs which are repeated in the consecutive season(Retained Team).
3. The disctinct count of application IDs which are not present in the previous season but present in the selected season(New Team).

 

The dashboard will be filtered for Seasons, when we select a particular season then i would want to know the count of Teams(application IDs) which are New and Retained from the previous year.

 


Thanks in advance.
Abhi

Need Help
Tips and Tricks
  • amitchandak's avatar
    amitchandak
    4 years ago

    Anonymous , Create a separate table with distinct seasons (say date) and create a rank on top of it

     

    seasons Rank = RANKX(all('Date'),'Date'[Year Start date],,ASC,Dense)

     

    new measures

    This seasons = CALCULATE(sum('Table'[Application ID]), FILTER(ALL('Date'),'Date'[seasons Rank]=max('Date'[seasons Rank])))
    Last seasons = CALCULATE(sum('Table'[Application ID]), FILTER(ALL('Date'),'Date'[seasons Rank]=max('Date'[seasons Rank])-1))

     

    Lost = Sumx(VALUES(Table[Application ID]),if(ISBLANK([This seasons]) && not(ISBLANK([Last seasons])) , 1,BLANK()))

     

    New = sumx(VALUES(Table[Application ID]), if(ISBLANK([Last seasons]) && not(ISBLANK([This seasons])) ,1,BLANK()))

     

    Retained = Sumx(VALUES(Table[Application ID]), if(not(ISBLANK([This seasons])) && not(ISBLANK([Last seasons])) , 1,BLANK()) )

     

  • v-zhangti's avatar
    v-zhangti
    4 years ago

    Hi, Anonymous 

     

    You can try the following methods.

    Measure:

    Count =
CALCULATE (
    COUNT ( 'Table'[Application ID] ),
    FILTER (
        ALL ( 'Table' ),
        [Application ID] = SELECTEDVALUE ( 'Table'[Application ID] )
            && [Session] <= SELECTEDVALUE ( 'Table'[Session] )
    )
)

    All subsequent calculations are based on Count Measure.

     

    In response to your first question.

    Count 1 = 
CALCULATE(DISTINCTCOUNT('Table'[Application ID]),
ALLEXCEPT('Table','Table'[Application ID]))

     

    In response to your second question.

    Count 2 =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Application ID] ),
    FILTER ( ALL ( 'Table' ), [Count] >= 2 )
)

     

    In response to your third question.

    New =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Application ID] ),
    FILTER (
        ALL ( 'Table' ),
        [Season] = SELECTEDVALUE ( 'Table'[Season] )
            && [Count] = 1
    )
)

    Retained = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Application ID] ),
    FILTER (
        ALL ( 'Table' ),
        [Season] = SELECTEDVALUE ( 'Table'[Season] )
            && [Count] >= 2
    )
)

             

    Are these the results you expect?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , Create a separate table with distinct seasons (say date) and create a rank on top of it

     

    seasons Rank = RANKX(all('Date'),'Date'[Year Start date],,ASC,Dense)

     

    new measures

    This seasons = CALCULATE(sum('Table'[Application ID]), FILTER(ALL('Date'),'Date'[seasons Rank]=max('Date'[seasons Rank])))
    Last seasons = CALCULATE(sum('Table'[Application ID]), FILTER(ALL('Date'),'Date'[seasons Rank]=max('Date'[seasons Rank])-1))

     

    Lost = Sumx(VALUES(Table[Application ID]),if(ISBLANK([This seasons]) && not(ISBLANK([Last seasons])) , 1,BLANK()))

     

    New = sumx(VALUES(Table[Application ID]), if(ISBLANK([Last seasons]) && not(ISBLANK([This seasons])) ,1,BLANK()))

     

    Retained = Sumx(VALUES(Table[Application ID]), if(not(ISBLANK([This seasons])) && not(ISBLANK([Last seasons])) , 1,BLANK()) )

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Amit amitchandak ,

      Thank you very much for the below solution, i have an issue where few of the records under "Season" are blank and the Rank formula is assigning Rank 1 to the blank records.
      How do i address this? how to ignore the blank records and assign Rank only for the records with "Season Field"?

      Thanks in advance.

  • FarhanAhmed's avatar
    FarhanAhmed
    Community Champion
    4 years ago

    Can you please check file attached.

     

    In power query you need to do following

    - Create group based on season with aggregation as ALL ROWS

    - Create Index based on groups

    - Expand the table

    - Now you have index based on your season

     

    in DAX you need to create a calculated column which will return whether team is retained or new using below DAX

     

     

    New/Retained = 

Var i= Season[Index]
var a = Season[Application ID]
var t  =CALCULATE(COUNTROWS(Season),FILTER(Season,Season[Application ID]=a && Season[Index]= i-1))

RETURN 

IF(t>0,"Retained","New")

     

     

    final output will be like this 

     

     

     

  • v-zhangti's avatar
    v-zhangti
    Community Support
    4 years ago

    Hi, Anonymous 

     

    You can try the following methods.

    Measure:

    Count =
CALCULATE (
    COUNT ( 'Table'[Application ID] ),
    FILTER (
        ALL ( 'Table' ),
        [Application ID] = SELECTEDVALUE ( 'Table'[Application ID] )
            && [Session] <= SELECTEDVALUE ( 'Table'[Session] )
    )
)

    All subsequent calculations are based on Count Measure.

     

    In response to your first question.

    Count 1 = 
CALCULATE(DISTINCTCOUNT('Table'[Application ID]),
ALLEXCEPT('Table','Table'[Application ID]))

     

    In response to your second question.

    Count 2 =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Application ID] ),
    FILTER ( ALL ( 'Table' ), [Count] >= 2 )
)

     

    In response to your third question.

    New =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Application ID] ),
    FILTER (
        ALL ( 'Table' ),
        [Season] = SELECTEDVALUE ( 'Table'[Season] )
            && [Count] = 1
    )
)

    Retained = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Application ID] ),
    FILTER (
        ALL ( 'Table' ),
        [Season] = SELECTEDVALUE ( 'Table'[Season] )
            && [Count] >= 2
    )
)

             

    Are these the results you expect?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.