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.
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 -
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’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.
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 -
Row Headers.
Column Headers.
Values.
And than hide the columns that were not required at all. So in this case, I had hidden three columns -
Attainment Grade - Overal Average for the Report Cycle - 202021.1
Attainment Grade - Overal Average for the Report Cycle - 202021.2
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.
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.
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.
