Forum Discussion

mscantle's avatar
mscantle
Icon for Helper I rankHelper I
2 years ago
Solved

Power BI table with rows filtering a dataset

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?

Tips and Tricks
  • mscantle's avatar
    mscantle
    2 years ago

    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.

10 Replies

    • mscantle's avatar
      mscantle
      Icon for Helper I rankHelper I
      2 years ago

      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.

  • ravsha85's avatar
    ravsha85
    Frequent Visitor
    2 years ago

    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's avatar
      mscantle
      Icon for Helper I rankHelper I
      2 years ago

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

      • ravsha85's avatar
        ravsha85
        Frequent Visitor
        2 years ago

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

         

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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