Sum the averages of multiple categories

[Anonomous]

Hello! I'm struggling to come up with a DAX formula (measure or calculated column) that will calculate the Sum of the average score for each evaluation area.

In my scenario, there are 3 people giving scores to 2 different companies based on 3 different evaluation areas (e.g., John scores Company X and Company Y based on criteria 1, 2 and 3). I want to calculate the average score of each evaluation area by company, and then sum those averages (so the sum of averages for each company). I'm hoping to make the formula dynamic so that if certain scorers or eval areas are filtered out, the end result would change accordingly. Thank you very much!

The end result will look like the last column in the table below. The calculations should look like this:

Company X: 

Avg score of eval area 1 = 2.83

Avg score of Eval area 2 = 2.50

Avg score of Eval area 3 = 3.50

Sum of all eval areas = 8.83

Company Y:

Avg score of eval area 1 = 3.33

Avg score of Eval area 2 = 2.67

Avg score of Eval area 3 = 3.0

Sum of all eval areas = 9.0

Evaluation Area	Scorer	Score	Company	Calculated column/measure desired result
Eval area 1	Andy	3	Company X	8.83
Eval area 1	John	4	Company X	8.83
Eval area 1	Beth	1	Company X	8.83
Eval area 2	Andy	2	Company X	8.83
Eval area 2	John	3	Company X	8.83
Eval area 2	Beth	1	Company X	8.83
Eval area 3	Andy	5	Company X	8.83
Eval area 3	John	1	Company X	8.83
Eval area 3	Beth	2	Company X	8.83
Eval area 1	Andy	5	Company Y	9.0
Eval area 1	John	1	Company Y	9.0
Eval area 1	Beth	3	Company Y	9.0
Eval area 2	Andy	2	Company Y	9.0
Eval area 2	John	4	Company Y	9.0
Eval area 2	Beth	5	Company Y	9.0
Eval area 3	Andy	3	Company Y	9.0
Eval area 3	John	2	Company Y	9.0
Eval area 3	Beth	1	Company Y	9.0

[Anonomous]

This is exactly what I was looking for, thank you very much!

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file if it suits your requirement.

Expected result measure: =
IF (
    HASONEVALUE ( 'Evaluation Area'[Evaluation Area] ),
    AVERAGE ( Data[Score] ),
    SUMX (
        VALUES ( 'Evaluation Area'[Evaluation Area] ),
        CALCULATE ( AVERAGE ( Data[Score] ) )
    )
)