Average

[Riddell2022]

Good morning 

I'm having an issue with finding a dax calculation to do something similar to excel please help!

I have a list of students 300 to be exact. Each student has been banded in to a band numbered 1 to 5. I currently have a column with this in power bi table. I then have a list of subjects associated to each student with an assessment point score so for example ID 1 has 6 results from 6 subjects including English with a score of 45. From this list of data I can see that all 300 students have a result in English but with varying outcomes. I need a column that gives me the average score of each subject so that I can subtract the students score from to give me the difference between students average and the average of the band. So for example is ID 1 had 45 and they were in band 1 and band 1s English average was 50 then the difference would be -5. (I'm using a remote session so it's difficult to present data )

[Anonymous]

Hi @Riddell2022 

Has your problem been solved ? If it has been solved, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regard

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Riddell2022 

Can you provide a detailed data to show what you describe ? Are all the data you mentioned in one table ? If not, how are they related ? To make the issue easier, I hope you can provide a sample .

Best Regard

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Riddell2022 ,

a new column

averageX(filter(Table, [Band] =earlier([Band]) && [subject] =earlier([subject]) ), [score])

New measures

averageX(filter(allselected(Table), [Band] =max([Band]) && [subject] =max([subject]) ), [score])