Forum Discussion
Calculating correct Average
Hello everyone,
I have a table containing project ratings (scores) for each row.
Each project was rated multiple times, and the rows are not ordered by project.
It's similar to this:
| ID Rating | ID Project | Score | Average expected | Average calculated so far |
| 1 | 3 | 27 | 40,58 | 47,61 |
| 2 | 2 | 71.5 | 53,08 | 47,61 |
| 3 | 2 | 29.75 | 53,08 | 47,61 |
| 4 | 1 | 29.25 | 29,5 | 47,61 |
| 5 | 3 | 50.5 | 40,58 | 47,61 |
6 | 3 | 44.25 | 40,58 | 47,61 |
| 7 | 2 | 58 | 53,08 | 47,61 |
| 8 | 1 | 26.75 | 29,5 | 47,61 |
I'm struggling to calculate the score average for each project. I've already tried:
AVERAGE_Pj =
VAR Project = DISTINCT('Avaliações'[ID Project])
RETURN
AVERAGEX (
Project,
CALCULATE (
AVERAGE ( 'Ratings'[final_score]),
FILTER (
'Ratings',
'Ratings'[ID Project] = EARLIER ( 'Avaliações'[ID Project] )
)
)
)
But it seems it's calculating the average for the entire column.
What am I missing?
Thanks in advance.
Kelvin_Aurelio - As long as I understand what you're trying to do correctly, use this (just refit to your table name):
AVERAGEX( VALUES( 'Table'[ID Project] ), CALCULATE(AVERAGE( 'Table'[Score] ), REMOVEFILTERS( 'Table'[ID Rating]) ))PBIX attached to show you my results are correct - your expected 29.5 for Project 1 is incorrect.
If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!
Hello Kelvin_Aurelio
You can use the following DAX to create a calculated column that will give you the correct average.
Average Actual = AVERAGEX ( FILTER ( 'Table', 'Table'[ID Project] = EARLIER ( 'Table'[ID Project] ) ), 'Table'[Score] )Please note that based on your dataset, the project-1 average is 28
Here is the screenshot of the calculated column along with the DAX:
I am also attaching the Power BI file for your reference.
Best Regards,
Udit
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍
🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree
Proud to be a Super User
6 Replies
Hello Kelvin_Aurelio
You can use the following DAX to create a calculated column that will give you the correct average.
Average Actual = AVERAGEX ( FILTER ( 'Table', 'Table'[ID Project] = EARLIER ( 'Table'[ID Project] ) ), 'Table'[Score] )Please note that based on your dataset, the project-1 average is 28
Here is the screenshot of the calculated column along with the DAX:
I am also attaching the Power BI file for your reference.
Best Regards,
Udit
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍
🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree
Proud to be a Super UserIt worked here, thanks!
Kelvin_Aurelio - As long as I understand what you're trying to do correctly, use this (just refit to your table name):
AVERAGEX( VALUES( 'Table'[ID Project] ), CALCULATE(AVERAGE( 'Table'[Score] ), REMOVEFILTERS( 'Table'[ID Rating]) ))PBIX attached to show you my results are correct - your expected 29.5 for Project 1 is incorrect.
If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!
For some reason I got the same results as mine in the example.
But thanks for trying 🙂
Kelvin_Aurelio - You need to use my solution in a measure not a calulated column.
Measures are Dynamic (and therefore can be affected by filtering) and are more optimal than calculated columns because they do not create additional data in your model and they do not need to be recalculated at the point of a refresh.
In the file I attached, you will have seen I used a measure. This is considered best practice, so you should think about using these more as you learn Power BI.
I think what you want is average.
you can add a new calculated column.
avg =
VAR _current = 'score'[ID Project]
VAR _avg =
ROUND (
AVERAGEX (
FILTER (
'score',
'score'[ID Project] = _current
),
'score'[Score]
),
2
)
RETURN
_avg
