Compute a calculation over SUMMARIZE in DAX

Question

I have the following scenario. I have students who pass test. They may have more than 1 supervisor at the same time. I would like to create a calculation in DAX that computes the average score at every level (i.e. department, supervisor, student).

The original table contains a single test per student, but I've "left joined" this table with a newly created one, student-supervisor, so I can compute also the score over the supervisor. The problem is when I compute the average score per department, because it contains all the duplicates I created with this new table.

These are my tables:

And this is my model:

The obvious DAX that just computes the average of the score works fine for Students and Supervisors on the PivotTable below, but it's wrong at a department level:

Avg Score:=AVERAGE(score[Score])

At this point I've tried something like the following, but without success. My point was to create a dynamic table with SUMMARIZE that groups by testid and does the average of score. Then I wanted to average that again, which would be the correct score and convert that 1column-1row into a numeric value. But this doesn't work, and I'm not sure why:

Avg Score= VAR ThisTable=SUMMARIZE(score,score[TestId],"IndividualScore",AVERAGE(score[Score])) RETURN SUMMARIZE(ThisTable,"AvgScore",AVERAGE([IndividualScore]))

alejandro zuleta alejandro zuleta · Accepted Answer · 2017-01-30T17:03:40

This is the way I'd approach it.

First create a measure like the below to get the score in each context:

Sum Score := MAX(Score[Score])

Then create the average calculation measure:

Avg Score :=
DIVIDE (
    SUMX ( DISTINCT ( Score[Student] ), [Sum Score] ),
    DISTINCTCOUNT ( Score[Student] )
)

Note the Sum Score measure is required because the Avg Score measure uses it to perform the calculation.

You will get something like this Matrix (Pivot Table) in Power BI:

Let me know if this helps.

Compute a calculation over SUMMARIZE in DAX

2 Answers