How to create measure to calculate rentention rate by advisor and location in POWER BI

[Poojabhardwaj20]

Hello everyone

I am here to request a help.

I am looking for a solution to calculate retention rate for  task having "Task Outcome" as won by advisor and by location for below table in Power BI

For example : Retention Rate for Ronald = Total won task by Ronald / Total task assigned to Ronald 

Retention Rate For location winnipeg = Total won task at Winnipeg / Total task at winnipeg  

TaskID | TaskType | Branch  | Advisor    | Task Outcomes|

1           MR            WPG       Ronald           Won

2           MR            WPG      Ronald    -        Lost

3           MR            WPG      Priscilla             Won

4           MR            WPG      Priscilla              Lost

5           MR            St.An      Justin                Won

6           MR            ST.An      Justin                Won

Thanks in advance

Regards

Pooja Sharma

[v-ssriganesh]

Hi @Poojabhardwaj20,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-ssriganesh]

Hi @Poojabhardwaj20,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-ssriganesh · Accepted solution]

Hi @Poojabhardwaj20,
Thank you for reaching out to the Microsoft Fabric Forum Community.

Please use the below DAX queries to find RetentionRatebyAdvisor and RetentionRatebyLocation  as per your requirements.

Retention Rate by Advisor:

RetentionRatebyAdvisor =

DIVIDE(

    CALCULATE(COUNTROWS(Sheet5), 'sheet5'[Task Outcomes] = "Won"),

    CALCULATE(COUNTROWS('Sheet5'))

)

Retention Rate by Location:

RetentionRatebyLocation =

DIVIDE(

    CALCULATE(COUNTROWS('Sheet5'), 'Sheet5'[Task Outcomes] = "Won"),

    CALCULATE(COUNTROWS('Sheet5'))

)

For your reference I have attached output screenshot and .pbix file:

If this information is helpful, please “Accept it as a solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[v-ssriganesh]

Hi @Poojabhardwaj20,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please accept it as a solution and give it a 'Kudos' so other community members with similar problems can find a solution faster.
Thank you.

[Deku]

If you have a table with person or location, this measure will give the the relation rate for each 

Retention Rate

Var won = 

Calculate( 

Countrows( table ),

Table[task] = "won"

)

Var total = 

Countrows( table )

Return

Divide( won, total )