Power BI table with rows filtering a dataset

[mscantle]

I have a dataset of values for academic performance of high school students in various subjects (Maths, English, Science, etc).

Each student belongs to one or more key groups (eg Male, Female, Special Needs, etc).

I want to create a table that has a row for each key group of students, and columns showing the average scores those students achieved in each subject.

What would be the best way to achieve this?

[Anonymous]

Hi @mscantle ,
Based on the data you provided, I did the following, and you can try to see if these steps meet your requirements
Let's Start with reverse perspective:
Open power query and choose both Gender and Special Need column and click unpviot column

Close and apply and then ensure there is a relationship between two tables with Addmission Number

Create a matrix visualiztion and change the calculate to average

Use date column to create a slicer
Final output

Best regards,
Albert He

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

[ravsha85]

@mscantle  you can try to create a Summarized table based on the Inputs you have 

SummaryTable =
SUMMARIZE (
'StudentTable',
'StudentTable'[KeyGroup],
"Maths Avg", AVERAGE ( 'StudentTable'[Maths] ),
"English Avg", AVERAGE ( 'StudentTable'[English] ),
"Science Avg", AVERAGE ( 'StudentTable'[Science] )
)

[mscantle]

Thanks ravsha85 - this looks like a good solution. How would I add multiple KeyGroups?

[ravsha85]

Based on your table structure I have created a sample , you could use the drill down approach to see it based on each keygroup

[mscantle]

Sorry, but where is this visualization?

[ravsha85]

@mscantle  Pls use Matrix Visual and let me know if you still face any issues

[mscantle · Accepted solution]

Thanks, but this doesn't solve the problem.

I have ended up creating lots of measures and then arranging them in Cards as follows:

The slicers then all work.

I just can't help thinking this a very inefficient way of getting what I want.

[mscantle]

Thanks, but this isn't quite what I need.

Here's a sample of the grades we collect for the students (we do this several times per year):

Here's a sample of the student group information we have:

I would like something that averages the grades for each group that looks like this:

However, I also want to slice it by date (2023a, 2023b, etc)

Sorry if this is way different to my original description, but if you can help, that would be amazing.

[PIYUSHPRAJAPATI]

Can you please provide the sample data because that would be give more idea....

[mscantle]

Unfortunately, as the data is sensitive, I can't give you the data. However, I have two tables. One contains details about the students (name, gender, special need, etc), and another table containing the exam results for each student in each subject. The tables are linked using the admission number of the students (a unique identifier). I don't know if this is enough to help you understand the problem, but thanks for trying.