Z-Score calculation error in DAX?

[AC23VM]

VC_Yes_Flag = 
IF(
'Outcomes Testing Data'[Vulnerable Customer] = "Vulnerable Customer",
1,
0
)

Grading_Numerical_Column = 
SWITCH(
TRUE(),
'Outcomes Testing Data'[Overall Journey Grading A-E] = "A. No Harm", 1,
'Outcomes Testing Data'[Overall Journey Grading A-E] = "B. No Harm - Observations", 2,
'Outcomes Testing Data'[Overall Journey Grading A-E] = "C. Potential Harm B", 3,
'Outcomes Testing Data'[Overall Journey Grading A-E] = "D. Potential Harm A", 4,
'Outcomes Testing Data'[Overall Journey Grading A-E] = "E. Actual Harm", 5,
BLANK()
)

Mean_Harm_Vulnerable = 
CALCULATE(
AVERAGE('Outcomes Testing Data'[Grading_Numerical_Column]),
FILTER(
'Outcomes Testing Data',
'Outcomes Testing Data'[VC_Yes_Flag] = 1 &&
'Outcomes Testing Data'[Overall Journey Grading A-E] IN {
"C. Potential Harm B",
"D. Potential Harm A",
"E. Actual Harm"
}
)
)


StdDev_Harm_Vulnerable = 
CALCULATE(
    STDEV.P('Outcomes Testing Data'[Grading_Numerical_Column]),
    FILTER(
        'Outcomes Testing Data',
        'Outcomes Testing Data'[VC_Yes_Flag] = 1 &&
        'Outcomes Testing Data'[Overall Journey Grading A-E] IN {"C. Potential Harm B", "D. Potential Harm A", "E. Actual Harm"}
    )
)


Z_Score_Harm_Vulnerable = 
VAR MeanValue = [Mean_Harm_Vulnerable]
VAR StdDevValue = [StdDev_Harm_Vulnerable]
VAR GradingValue = MAX('Outcomes Testing Data'[Grading_Numerical_Column])
RETURN
IF(
    ISBLANK(GradingValue) || StdDevValue = 0,
    BLANK(),
    (GradingValue - MeanValue) / StdDevValue
)

Hi there.  I recently got some help with DAX code to produce an output. While the code works, I don't think it's doing exactly what I need. When I manually calculate the Z-Score, I get around 0.01, but the DAX code gives me 3.98.

I suspect the issue is with how the Grading_Numerical_Column is being used. This column was created to convert text data (the gradings) into numerical values so I could calculate the volumes of each number. However, it seems the DAX code might be calculating the actual values instead of just counting the occurrences. Since the Z-Score formula involves the mean and standard deviation of these numerical values, the calculation might be influenced by the values themselves rather than just the count of occurrences. This could explain the big difference between my manual calculation and the DAX output.

Could anyone please check this DAX and see where I might be going wrong?

[v-nmadadi-msft]

Hi @AC23VM ,

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.

If we don’t hear back, we’ll go ahead and close this thread. For any further discussions or questions, please start a new thread in the Microsoft Fabric Community Forum  we’ll be happy to assist.

Thank you for being part of the Microsoft Fabric Community.

[lbendlin]

Likely this part

Mean_Harm_Vulnerable = 
CALCULATE(
AVERAGE('Outcomes Testing Data'[Grading_Numerical_Column]),

You would need to replace that with COUNTROWS across all categories. 

[AC23VM]

I don't understand, @lbendlin, sorry.  I can't replace the CALCULATE with COUNTROWS because then I'm not getting the mean.  But I also can't add in COUNTROWS because then it's too many arguments. 

[v-nmadadi-msft]

Hi @AC23VM,
Thanks for reaching out to the Microsoft fabric community forum.

The issue seems to come from a mismatch between what you're trying to measure and how the DAX is written. Right now, the calculation takes an average of numbers that were assigned to each grading category. But those numbers are just labels, not real values you can average in a meaningful way. So, the DAX treats them like actual measurements, which leads to incorrect results.

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards