topic Re: Calculating correct Average in DAX Commands and Tips

Calculating correct Average

Kelvin_Aurelio — Thu, 27 Feb 2025 15:20:24 GMT

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.

Re: Calculating correct Average

mark_endicott — Thu, 27 Feb 2025 16:15:53 GMT

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!

Re: Calculating correct Average

weiqb — Thu, 27 Feb 2025 16:25:16 GMT

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

Re: Calculating correct Average

quantumudit — Thu, 27 Feb 2025 16:27:02 GMT

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

Re: Calculating correct Average

Kelvin_Aurelio — Thu, 27 Feb 2025 16:37:27 GMT

It worked here, thanks!

Re: Calculating correct Average

Kelvin_Aurelio — Thu, 27 Feb 2025 16:38:42 GMT

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

But thanks for trying 🙂

Re: Calculating correct Average

mark_endicott — Thu, 27 Feb 2025 16:56:29 GMT

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.