DAX Function to Calculate Average Scores for Strategic Risks

Overview

This document outlines a DAX function designed to calculate the average scores of strategic risks across three specific variables: 284, 286, and 288. The function will aggregate the data while ensuring clarity and efficiency.

Assumptions

• The data source includes a table (e.g., RiskData) containing columns for strategic risks and the relevant score variables (284, 286, and 288).
• Each score variable is numeric.

Function Definition

AverageStrategicRiskScores = 
VAR TotalRiskScores =
    SUMX(
        RiskData,
        RiskData[Score284] + RiskData[Score286] + RiskData[Score288]
    )
VAR CountRiskScores =
    COUNTROWS(RiskData)
RETURN
    IF(
        CountRiskScores > 0,
        TotalRiskScores / (CountRiskScores * 3),
        BLANK()
    )

Documentation

• Function Name: AverageStrategicRiskScores
• Purpose: Calculates the average score across specified strategic risk variables (284, 286, 288).
• Returns: A decimal number representing the average score, or BLANK if there are no entries.
• Parameters: None (uses context from RiskData table).
• Exceptions: Returns BLANK if there are no valid rows in the dataset.

Explanation of Logic

1. Variable Definitions:

   • TotalRiskScores: Computes the cumulative sum of scores for the three specified risk categories across all rows in the RiskData table, utilizing SUMX for row-wise calculations.
   • CountRiskScores: Counts the number of rows present in the RiskData table to determine if any scores exist.

2. Return Statement:

   • Uses an IF condition to check if CountRiskScores is greater than zero. If so, it divides the TotalRiskScores by the product of CountRiskScores and the number of variables (which is 3) to compute the average.
   • If there are no rows, it returns BLANK() to avoid division by zero.

Usage Example

To utilize the AverageStrategicRiskScores measure, include it in a report or visualization where you wish to display the average scores for strategic risks:

EVALUATE 
SUMMARIZE(
    RiskData,
    RiskData[StrategicRisk],
    "Average Risk Score", [AverageStrategicRiskScores]
)

Key Notes

• Ensure the RiskData table is loaded into your Power BI model and contains the score fields (Score284, Score286, Score288).
• The measure can be easily extended or modified if additional score variables need to be included.

Conclusion

This DAX function efficiently calculates the average scores of strategic risk assessments across multiple variables, following best practices in DAX coding. For further learning on DAX and data modeling, consider engaging with resources available on the Enterprise DNA Platform.