Forum Discussion

Shaun103's avatar
Shaun103
Regular Visitor
2 years ago

Dynamic retention table and switching fields with a slicer.

I'm currently attempting to make a table that allows me to calcualte retention based on various fields.

 

My goal is to be able to switch the field in the rows via a slicer. 

 

Here is the layout of the table, with account tenure month in the columns and field (in this picture start month) in the rows. 

 

Here are the fields I want to be able to switch between.

 

I have already created a parameter for the fields and a table index. 

 

Parameter: 

 

 

Account Retention Dimensions = {
    ("Start_Cohort_grid", NAMEOF('Account_Overview_Retention_Grid'[Start_Cohort_grid]), 0),
    ("industry", NAMEOF('Account_Overview_Retention_Grid'[industry]), 1),
    ("prepay_flag", NAMEOF('Account_Overview_Retention_Grid'[prepay_flag]), 2),
    ("contract_term__c", NAMEOF('Account_Overview_Retention_Grid'[contract_term__c]), 3),
    ("ASP_bucket", NAMEOF('Account_Overview_Retention_Grid'[ASP_bucket]), 4),
    ("Total_Users_Bucket", NAMEOF('Account_Overview_Retention_Grid'[Total_Users_Bucket]), 5),
    ("Bucket_Avg_Checkins_fst_3_Month", NAMEOF('Account_Overview_Retention_Grid'[Bucket_Avg_Checkins_fst_3_Month]), 6),
    ("Bucket_Avg_requests_total_fst_3_Month", NAMEOF('Account_Overview_Retention_Grid'[Bucket_Avg_requests_total_fst_3_Month]), 7),
    ("Bucket_Avg_Reviews_Total_fst_3_Month", NAMEOF('Account_Overview_Retention_Grid'[Bucket_Avg_Reviews_Total_fst_3_Month]), 8),
    ("Bucket_Avg_Reviews_Worksite_fst_3_Month", NAMEOF('Account_Overview_Retention_Grid'[Bucket_Avg_Reviews_Worksite_fst_3_Month]), 9)
}

 

 

Table Index: 

 

 

DimensionNamesTable = 
DATATABLE(
    "DimensionName", STRING,
    {
        {"Start_Cohort_grid"},
        {"industry"},
        {"prepay_flag"},
        {"contract_term__c"},
        {"ASP_bucket"},
        {"Total_Users_Bucket"},
        {"Bucket_Avg_Checkins_fst_3_Month"},
        {"Bucket_Avg_requests_total_fst_3_Month"},
        {"Bucket_Avg_Reviews_Total_fst_3_Month"},
        {"Bucket_Avg_Reviews_Worksite_fst_3_Month"}
    }
)

 

 

 

I have also built out measures to calcualte retention for each of these fields individually. Here is an example of one, the formating is the same for all fields. 

 

 

RetentionRateByIndustry = 
VAR MaxTenureMonth = MAX('Account_Overview_Retention_Grid'[Tenure_Month])
VAR Denominator =
    CALCULATE(
        DISTINCTCOUNT('Account_Overview_Retention_Grid'[Account_Safe_ID]),
        ALL('Account_Overview_Retention_Grid'),
        'Account_Overview_Retention_Grid'[Tenure_Month] <= MaxTenureMonth,
        'Account_Overview_Retention_Grid'[industry] = SELECTEDVALUE('Account_Overview_Retention_Grid'[industry])
    )
VAR Numerator =
    CALCULATE(
        SUM('Account_Overview_Retention_Grid'[Active_At_BOM]),
        ALL('Account_Overview_Retention_Grid'),
        'Account_Overview_Retention_Grid'[Tenure_Month] = MaxTenureMonth,
        'Account_Overview_Retention_Grid'[industry] = SELECTEDVALUE('Account_Overview_Retention_Grid'[industry])
    )
RETURN
IF(
    Denominator > 0,
    DIVIDE(Numerator, Denominator),
    BLANK()
)

 

 

 

I then created another measure  to pull the corresponding calculations based on the field slected in the slicer. However this did not work.

 

 

Dynamic_RetentionRate 2 = 
VAR SelectedDimension = SELECTEDVALUE(DimensionNamesTable[DimensionName])
VAR MaxTenureMonth = MAX('Account_Overview_Retention_Grid'[Tenure_Month])

RETURN
SWITCH(
    SelectedDimension,
    "Start_Cohort_grid", [RetentionRateByStartCohort],
    "industry", [RetentionRateByIndustry],
    "prepay_flag", [RetentionRateByPrePayFlag],
    "contract_term__c", [RetentionRateByContractTerm],
    "ASP_bucket", [RetentionRateByASP],
    "Total_Users_Bucket", [RetentionRateByTotalUsers],
    "Bucket_Avg_Checkins_fst_3_Month", [RetentionRateByAvgCheckins],
    "Bucket_Avg_requests_total_fst_3_Month", [RetentionRateByAvgRequests],
    "Bucket_Avg_Reviews_Total_fst_3_Month", [RetentionRateByTotalReviews],
    "Bucket_Avg_Reviews_Worksite_fst_3_Month", [RetentionRateByWorksiteReviews],
    BLANK()  // Default result if none of the cases match
)

 

 

 

In sum, the retention calculations for each field individually work great, but having trouble grouping them in way where I can filter between them in the same view.

 

Any insight or furthur direction would be greatly appreciated! Also happy to provide more context.

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    2 years ago

    Shaun103 , Is retention like values: New, Retained, and old coming from a measure?

     

    In such case, you need dynamic segmentation using disconnected table

    example

    Customer Retention with Dynamic Segmentation, New/Lost/Retain Customer Count: https://youtu.be/EyL7KMw877Q

     

    If want switch even the measure for bucket

    then using this code you can get a selected measure

    Selectedvalue(Parameter [Parameter Order])

     

    Measn instead of name use value.

     

    • Shaun103's avatar
      Shaun103
      Regular Visitor
      2 years ago

      amitchandak Thanks for the response. 


      Retention is a calculation based on various fields within a dataset. Essentially, it is number of active accounts / total accounts. However, this calculation is conditional based on the tenure month, or month # in which accounts are subscribed, this field is static in the columns of the table. 

       

      In th rows, I want to be able to change the field by a range of catagorical values, which each require a new calcualtion. For example, if I select "industry", retention would be calculated for each industry.

       

      However, I can't seem to figure out how to make a Row field that changes with the slicer. I have tried to do so with the parameter and a table index but still no luck. 

       

      This is the value field I'm using:

      DynamicRetentionRate = 
VAR SelectedDimension = SELECTEDVALUE('DimensionNamesTable'[DimensionName], "Start_Cohort_grid")
RETURN
SWITCH(
    SelectedDimension,
    "Start_Cohort_grid", [RetentionRateByStartCohort],
    "industry", [RetentionRateByIndustry],
    "prepay_flag", [RetentionRateByPrePayFlag],
    "contract_term__c", [RetentionRateByContractTerm],
    "ASP_bucket", [RetentionRateByASP],
    "Total_Users_Bucket", [RetentionRateByTotalUsers],
    "Bucket_Avg_Checkins_fst_3_Month", [RetentionRateByAvgCheckins],
    "Bucket_Avg_requests_total_fst_3_Month", [RetentionRateByAvgRequests],
    "Bucket_Avg_Reviews_Total_fst_3_Month", [RetentionRateByTotalReviews],
    "Bucket_Avg_Reviews_Worksite_fst_3_Month", [RetentionRateByWorksiteReviews],
    [RetentionRateByStartCohort] // Default case if nothing is selected
)

       

      This is the "disconnected table" or "parameter table" used in the slicer and referenced in the value field: 

      DimensionNamesTable = 
DATATABLE(
    "DimensionName", STRING,
    "Dimension Index", INTEGER,
    {
        {"Start_Cohort_grid",0 },
        {"industry",1 },
        {"prepay_flag", 2},
        {"contract_term__c", 3},
        {"ASP_bucket", 4},
        {"Total_Users_Bucket", 5},
        {"Bucket_Avg_Checkins_fst_3_Month", 6},
        {"Bucket_Avg_requests_total_fst_3_Month", 7},
        {"Bucket_Avg_Reviews_Total_fst_3_Month", 8},
        {"Bucket_Avg_Reviews_Worksite_fst_3_Month", 9}
    }
)