DAX Formula for Adoption Rate

Objective

To create a DAX formula that calculates the adoption rate by dividing the sum of a specific metric (FNI 3M) by the sum of the total number of customers. The denominator should be adjusted to ensure it equals zero when the fund is not classified as "super".

DAX Formula

The following DAX formula computes the desired adoption rate while incorporating the condition for the fund classification:

Adoption Rate = 
VAR TotalFNI = SUM(customer_profile_digital_report[FNI_3M])
VAR TotalCustomers = 
    SUMX(
        customer_profile_digital_report,
        IF(customer_profile_digital_report[Fund_Type] = "super", customer_profile_digital_report[customers], 0)
    )
RETURN 
    DIVIDE(TotalFNI, TotalCustomers, 0)

Formula Explanation

1. Variables:

   • TotalFNI: Calculates the total of the column FNI_3M from the customer_profile_digital_report.
   • TotalCustomers: Utilizes the SUMX function to iterate over each row in the customer_profile_digital_report. It checks if the Fund_Type is "super". If true, it sums the customers; otherwise, it contributes zero.

2. DIVIDE Function:

   • The DIVIDE function is employed to perform the division operation (sum of FNI by sum of customers). The third parameter (0) specifies the value to return when the denominator is zero to prevent division errors.

Practical Example

Scenario

• Consider a dataset where the customer_profile_digital_report includes two types of funds: "super" and "non-super".
• Data Sample:

Calculation Steps

• TotalFNI Calculation:
  • 120 + 80 = 200
• TotalCustomers Calculation (only for "super"):
  • 30 + 20 = 50
• Adoption Rate Calculation:
  • Adoption Rate = 200 / 50 = 4.0

Output

The adoption rate displays as 4.0 for the given sample data, demonstrating the calculated metric's effectiveness.

Closing Note

This DAX formula efficiently calculates the adoption rate while maintaining the necessary condition for fund classification. For further learning on DAX and data modeling, consider exploring resources available on the Enterprise DNA platform to enhance your skills and comprehension.