Help with AverageX

Dear all,

I’m having some problems in creating an AverageX formula correctly. I’ve attached a PBI file with some sample data. In short, I need to filter a table to only have year 12/13 pupils, then sum the attainment grades for each pupil in each report cycle then average them.

I then want to use the measure to show the average of the sum of attainment grades for each pupil for a single report cycle, by pupil and so on. A screenshot of the desired output is in the PBI file and attached.

Any help would be much appreciated!
Thanks
Matt

AverageX help.pbix (48.7 KB)

Hello @corkemp,

Thank You for posting your query onto the Forum.

To achieve the results that you’ve showcased in the screenshot you’ll be required to write two measures. One will be to calculate the average of each individual year and other one will be to showcase overall average. Below are the formulas provided for the reference -

Attainment Grade - Individual Average = 
DIVIDE( 
    SUM( 'Report data'[Attainment grade] ) , 
    DISTINCTCOUNT( 'Report data'[Name] ) , 
    0 )



Attainment Grade - Overal Average = 
VAR Averages = 
DIVIDE( 
SUM( 'Report data'[Attainment grade] ) , 
DISTINCTCOUNT( 'Report data'[Subject] ) , 
0 )

RETURN
IF( HASONEVALUE( 'Report data'[Name] ) , 
Averages , 
DIVIDE( 
    Averages , 
    DISTINCTCOUNT( 'Report data'[Name] ) , 
    0 ) )

And than based on these simple measures you’ll be able to see the results that you’re trying to achieve. Below is the screenshot of the results provided for the reference -

I’m also attaching the working of the PBIX file for the reference.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

AverageX - Harsh - Revised.pbix (50.6 KB)

3 Likes

Thanks, Harsh!

I’ve put it inside Calculate to do the filtering on year group as well.

How do you get the Overall Average measure to only show in the total column?

Also, I’d like to be able to filter the table by the number of report grades a pupil has. In my example, all pupils have two grades per report cycle. However, if there were a few pupils with only one report grade, I don’t want them to be included. How would I go about doing that? I’m thinking of a virtual table for each pupil and report cycle with a count of their grades, then filtering that in the measure? Not sure how I would actually do this in practice though.

Thanks for your help.
Matt

Hello @corkemp,

How do you get the Overall Average measure to only show in the total column?

To achieve this, just turn of the “Word Wrap” option under the following menus -

  1. Row Headers.
  2. Column Headers.
  3. Values.

And than hide the columns that were not required at all. So in this case, I had hidden three columns -

  1. Attainment Grade - Overal Average for the Report Cycle - 202021.1
  2. Attainment Grade - Overal Average for the Report Cycle - 202021.2
  3. Attainment Grade - Individual Average - Total.

Now, for the next part of the question -

You can make use of “ISBLANK()” function, if you have only one of the report grades. That is, if report grade for cycle 1 is blank than show the cycle 2 as blank as well and same approach goes for vice-versa.

Hoping you find this useful. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

Hi Harsh,

Thanks for explaining the total column setup!

I’m simplifying a bit for the count filter. In any one report cycle a pupil in years 12 or 13 should have six reports (one per subject). If they have less than six, it just won’t exist rather than be blank. So I need to count the number of subjects per report cycle per student. If any student has less than 6, then don’t include that student in the calculation.

Thanks,
Matt

Hello @corkemp,

Than you can use the “IF()” or “SWITCH( TRUE() )” functions for this type of conditions and than ignore that student.

Also if your original query has been resolved than please mark the post as solved and close the thread and create a new thread for addidtional questions by providing the PBIX file alongwith the results that you’re trying to achieve.

Thanks and Warm Regards,
Harsh

Need a bit more help with this one - I’ll sort out some dummy data and post as a new topic.

Thanks again.
Matt