Sum the averages of multiple categories

Anonomous — Sat, 09 Dec 2023 01:08:44 GMT

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

Re: Sum the averages of multiple categories

Jihwan_Kim — Sat, 09 Dec 2023 06:59:26 GMT

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

Re: Sum the averages of multiple categories

Anonomous — Mon, 11 Dec 2023 16:36:42 GMT

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

topic Sum the averages of multiple categories in DAX Commands and Tips

Sum the averages of multiple categories

Re: Sum the averages of multiple categories

Re: Sum the averages of multiple categories