DAX Formula for Weighted Average of Active Members

DAX Formula: Weighted Average of Active Members

Formula

To calculate the weighted average number of active members, you can use the following DAX formula:

Weighted Avg Member no. = 
DIVIDE(
    SUMX(
        'Calendar',
        '*Membership Measures'[Actual Active Members]
    ),
    SUM('Membership Budget Forecast'[Active Member])
)

Explanation

• SUMX Function:

  • Iterates over a table (in this case, the 'Calendar' table).
  • For each row in the 'Calendar', it calculates the value of '*Membership Measures'[Actual Active Members]. This results in a total sum of 'Actual Active Members' for the defined context of 'Calendar'.

• SUM Function:

  • Calculates the total of 'Active Member' from the 'Membership Budget Forecast' table. This serves as the denominator in the weighted average computation.

• DIVIDE Function:

  • Safely performs division. It divides the result of the SUMX by the result of the SUM. If the denominator is zero, it will return an alternate result (default is BLANK), preventing errors caused by division by zero.

Practical Example

Consider the following datasets:

• Calendar Table: Contains dates from January to December.
• Membership Measures Table: Includes a column that tracks actual active members each month.
• Membership Budget Forecast Table: Contains predicted active members.

Assume:

• January has 100 actual active members.
• The forecast for January predicts 150 active members.

The calculation for January will be:

• Result of SUMX for January: 100 (from '*Membership Measures'[Actual Active Members])
• Result of SUM for January: 150 (from 'Membership Budget Forecast'[Active Member])

Applying the formula for January would result in: Weighted Avg Member no. = DIVIDE(100, 150) = 0.6667 or 66.67% for January.

Conclusion

The provided DAX formula effectively computes the weighted average of active members, allowing for dynamic analysis across time-based perspectives through the use of the 'Calendar' table. Users can implement this formula to enhance reporting and decision-making processes regarding membership trends. For further enhancing your DAX skills, consider leveraging the resources available on the Enterprise DNA Platform.