Forum Discussion

Kelvin_Aurelio's avatar
Kelvin_Aurelio
New Member
1 year ago
Solved

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 Rat...
  • mark_endicott's avatar
    mark_endicott
    1 year ago

    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's avatar
    quantumudit
    1 year ago

    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's avatar
Kelvin_Aurelio
New Member
1 year ago

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

But thanks for trying 🙂

mark_endicott's avatar
mark_endicott
Super User
1 year ago

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.