Calculating correct Average

[Kelvin_Aurelio]

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.

[mark_endicott · Accepted solution]

@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!

[quantumudit · Accepted solution]

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

[Kelvin_Aurelio]

It worked here, thanks!

[weiqb]

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

[mark_endicott · Accepted solution]

@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!

[Kelvin_Aurelio]

For some reason I got the same results as mine in the example.

But thanks for trying 🙂

[mark_endicott]

@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.