Grouped Z-Score using dimension from another table

[kris_cs1]

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 mean for the student per [Phys Content Area 1]:

CALCULATE(AVERAGE(fact_results[ResultPercentage]), ALLEXCEPT(dim_question, dim_question[Phys Content Area 1]))

I can get the mean for the cohort per [Phys Content Area 1]:

CALCULATE(AVERAGE(fact_results[ResultPercentage]), ALLEXCEPT(dim_question, dim_question[Phys Content Area 1]), ALLEXCEPT(fact_results, dim_question[Phys Content Area 1]))

But calculating the standard deviation is tricker because it first needs to average the results for each student per [Phys Content Area 1] before calculating the standard deviation. CHATGPT gives me this (which I have tried fixing), but I get 'Parameter is not the correct type.'

STDEV.P(

AVERAGEX(

SUMMARIZE(

ALLEXCEPT(fact_results, dim_question[Phys Content Area 1]),

dim_question[Phys Content Area 1],

"TotalResult",

AVERAGE(fact_results[ResultPercentage])

),

[TotalResult]

),

ALLEXCEPT(dim_question, dim_question[Phys Content Area 1])

)

 Thank you so much.

[kris_cs1 · Accepted solution]

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])