Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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] ) )
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
This is exactly what I was looking for, thank you very much!
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] ) )
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
40 | |
27 | |
20 | |
14 | |
8 |
User | Count |
---|---|
76 | |
48 | |
46 | |
20 | |
16 |