Forum Discussion
Z-Score in Table Matrix Visual
I am trying to show the Z-Score for each employee in a table matrix.
I am using slicers to filter for different zones employees are in.
I am not sure how to write the DAX for the following variables:
TotalTests
TotalEmployees
AverageTests
StddDev
With my slicer = "Zone 1"
Employee1 did 234 tests
Employee2 did 221 tests
Employee3 did 190 tests
Employee4 did 75 tests
Total Tests = 720
Total Employees = 4
TotalTests = 720
TotalEmployees = 4
AverageTests = 180
StddDev = STDEVX.P ( ? ) * should equal 72.39
ZScore = DIVIDE ( TotalTests - TotalEmployees , StddDev )
Desired Table Matrix:
| Employee | TotalTests | AverageTests | Standard Deviation | Z-Score | ||
| Employee1 | 234 | 180 | 72.39 | .75 | ||
| Employee2 | 221 | 180 | 72.39 | .57 | ||
| Employee3 | 190 | 180 | 72.39 | .14 | ||
| Employee4 | 75 | 180 | 72.39 | -1.45 |
7 Replies
//Replace table & Column with yours---This might help you Z-Score = VAR TotalTests = SUM(EmployeeData[Tests]) VAR TotalEmployees = COUNTROWS(FILTER(EmployeeData, [Zone] = SELECTEDVALUE('Slicer'[Zone]))) VAR AverageTests = AVERAGE(EmployeeData[Tests]) VAR StddDev = STDEV.P(FILTER(EmployeeData, [Zone] = SELECTEDVALUE('Slicer'[Zone]))[Tests]) RETURN DIVIDE(TotalTests - TotalEmployees, StddDev)Did you try this one
CountTests = Count(EmployeeData[Tests])
