Count and average of values in one column per value in another column

[HM615]

Hello! 

I am trying to calculate a cout of rows and an average column value for each value in another column. 

Data table looks like this: 

I need to: 

1. Calculate an average count of rows with a QualificationSource_BK of SQF per StudentCode_BK - so work out the average number per student code.

2. Work out the average score (ActualGrade) for the SQF rows for each StudentCode_BK. 

I've looked over so many webpages but can't find anything that quite fits what I'm trying to do! Thank you.

[HM615]

Thank you! That's absolutely perfect! 

[DataNinja777 · Accepted solution]

Hi @HM615 ,

You can accomplish this by creating two separate DAX measures in Power BI. Remember to replace the placeholder table name 'StudentData' with the actual name of your table in the formulas.

For your first requirement, calculating the average number of "SQF" qualifications per student, you can use the following measure. This formula first finds the number of "SQF" rows for each individual student and then averages those counts across all students.

Avg SQF Count per Student = 
AVERAGEX(
    VALUES('StudentData'[StudentCode_BK]),
    CALCULATE(
        COUNTROWS('StudentData'),
        'StudentData'[QualificationSource_BK] = "SQF"
    )
)

This DAX expression works by using VALUES to create a list of unique students. The AVERAGEX function then iterates over this list, and for each student, CALCULATE counts the rows that are filtered to only include those where QualificationSource_BK is "SQF". The final result is the average of these counts.

For your second task, to determine the average ActualGrade for the "SQF" rows, you can create another measure. This formula will calculate the average grade, but only for the rows that meet the "SQF" criteria.

Avg SQF Grade = 
CALCULATE(
    AVERAGE('StudentData'[ActualGrade]),
    'StudentData'[QualificationSource_BK] = "SQF"
)

Here, the CALCULATE function modifies the filter context before the AVERAGE function is evaluated. It tells Power BI to only consider rows from your data table where QualificationSource_BK is "SQF" when it calculates the average of the ActualGrade column.

To use these measures, create them in your Power BI model. The [Avg SQF Count per Student] measure returns a single number, so it's best displayed in a Card visual. The [Avg SQF Grade] measure can be used in a Table visual; simply add the StudentCode_BK column and this new measure to the table to see the average SQF grade for each student.

Best regards,