Forum Discussion

kris_cs1's avatar
kris_cs1
Frequent Visitor
3 years ago
Solved

Grouped Z-Score using dimension from another table

Hi, These are two of my tables joined on QuestionID: I want to calculate a Z-Score using the average of all questions within each [Phys Content Area 1] for each student.    I can get the me...
  • kris_cs1's avatar
    kris_cs1
    3 years ago

    It took me 3 hours of trying various things, but I finally got it working using this measure: 

    CALCULATE(STDEVX.P(FILTER(
    ADDCOLUMNS(SUMMARIZE(
    fact_results,
    fact_results[StudentID],
    dim_question[Phys Content Area 1]),
    "AverageResultPercentageStudent", CALCULATE(AVERAGE(fact_results[ResultPercentage]))
    )
    , dim_question[Phys Content Area 1] = dim_question[Phys Content Area 1] )
    , [AverageResultPercentageStudent]
    ), ALLEXCEPT(fact_results, dim_question[Phys Content Area 1])
kris_cs1's avatar
kris_cs1
Frequent Visitor
3 years ago

It took me 3 hours of trying various things, but I finally got it working using this measure: 

CALCULATE(STDEVX.P(FILTER(
ADDCOLUMNS(SUMMARIZE(
fact_results,
fact_results[StudentID],
dim_question[Phys Content Area 1]),
"AverageResultPercentageStudent", CALCULATE(AVERAGE(fact_results[ResultPercentage]))
)
, dim_question[Phys Content Area 1] = dim_question[Phys Content Area 1] )
, [AverageResultPercentageStudent]
), ALLEXCEPT(fact_results, dim_question[Phys Content Area 1])