Calculate Average for Subset of Cohort

[en2000]

Hey All,

I'm fairly new to PowerBI, so I'm doing my best to challenge myself in order to learn more.

I'm building a dashboard to display data for an individual student who has been selected from a slicer.  On the dashboard, is a bar chart that sums a particular value for the student for each year they have attended the school. 

Currently, if I understand correctly, the blue line is the average of all students for the year.  However, I want the blue line to represent the average for the selected students year level for each year.  I assume I need to create this in a measure, but I'm not sure where to start.  Any pointers would be great.  Cheers, E.

[en2000]

Thanks @v-jiewu-msft for your detailed and helpful reply.  I have been working on other priorities since I posted this question, so haven't had a chance to see if it works for my use case, but it looks very promising.  

[v-jiewu-msft]

Hi @en2000 ,

If the method above works, please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Wisdom Wu

[v-jiewu-msft · Accepted solution]

Hi @en2000 ,

First of all, many thanks to @Ashish_Mathur  for your very quick and effective replies, and I will give some additions below:

1.Create the sample table.

2.Create the measure to calculate average for the selected student.

Selected_student_avg = 
var stu = SELECTEDVALUE('Table'[Student])
VAR sco = CALCULATE(SUM('Table'[Score]), FILTER(ALLEXCEPT('Table', 'Table'[Year]), 'Table'[Student] = stu))
var stu_year = CALCULATE(SUM('Table'[Score]), FILTER(ALLEXCEPT('Table', 'Table'[Year]), MIN('Table'[Year])))
RETURN
DIVIDE(sco, stu_year)

3.Select the slicer visual and drag the student into the visual.

4.Drag the Year into the X-axis. Drag the score into the column y-axis. Drag the measure into the Line y-axis.

5.Select the student in the slicer. The result is shown below.

Best Regards,

Wisdom Wu

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

[en2000]

If I'm calculating the average per year level, per year, then the blue line would NOT be straight as the average could change.

The file has personal data, so I unfortunately cannot share.

[Ashish_Mathur]

Hi,

Should the blue line be a straight line then?  If my understanding is correct, then share the download link of the PBI file.