Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I've been playing around with this on and off for a week or so. It's almost right... but not quite.
I've got a model with pupils (I work in a school) with attendance, behaviour and assessment. I'm trying to work on a reading age test, the result of which gives us a reading age. The pupils are tested 3 times a year and I have the autumn and spring results in the model. I've got these results as reading age in months in Power BI. I can average this column - and when I add a term and class slicer - the average card I have front and centre on the page changes correctly. I'm then wanting to convert the age in months to Years/Months - but this is not working correctly when I slice the data by term and class. I'm not sure if the problem is with the average formula, the years/months formula or the relationships in the model.
My data looks something like this:
Reading Ages
ID | Years | Months | RA in Months | Test |
1 | 8 | 2 | 98 | Autumn |
2 | 14 | 7 | 175 | Autumn |
3 | 13 | 5 | 161 | Autumn |
4 | 17 | 204 | Autumn | |
5 | 13 | 5 | 161 | Autumn |
6 | 17 | 204 | Autumn | |
7 | 13 | 8 | 164 | Autumn |
8 | 14 | 7 | 175 | Autumn |
9 | 17 | 204 | Autumn | |
10 | 14 | 168 | Autumn | |
1 | 6 | 3 | 75 | Spring |
2 | 10 | 5 | 125 | Spring |
3 | 17 | 204 | Spring | |
4 | 10 | 3 | 123 | Spring |
5 | 15 | 2 | 182 | Spring |
6 | 17 | 204 | Spring | |
7 | 10 | 120 | Spring | |
8 | 17 | 204 | Spring | |
9 | 13 | 2 | 158 | Spring |
10 | 12 | 1 | 145 | Spring |
Pupil Data
ID | Forename | Surname | Free School Meals |
1 | Pupil | One | No |
2 | Pupil | Two | No |
3 | Pupil | Three | No |
4 | Pupil | Four | No |
5 | Pupil | Five | Yes |
6 | Pupil | Six | No |
7 | Pupil | Seven | No |
8 | Pupil | Eight | Yes |
9 | Pupil | Nine | No |
10 | Pupil | Ten | Yes |
Classes
ID | Class |
1 | 7French1 |
2 | 7French1 |
3 | 7French1 |
4 | 7French1 |
5 | 7French1 |
6 | 7French2 |
7 | 7French2 |
8 | 7French2 |
9 | 7French2 |
10 | 7French2 |
1 | 7English1 |
2 | 7English2 |
3 | 7English1 |
4 | 7English2 |
5 | 7English1 |
6 | 7English1 |
7 | 7English2 |
8 | 7English1 |
9 | 7English2 |
10 | 7English1 |
1 | 7Science2 |
2 | 7Science1 |
3 | 7Science2 |
4 | 7Science1 |
5 | 7Science2 |
6 | 7Science2 |
7 | 7Science1 |
8 | 7Science2 |
9 | 7Science1 |
10 | 7Science1 |
Relationships are Pupil Data ID with Reading Ages ID (one to many) and Reading Ages ID with Classes ID (many to many).
Average formula is (I've tried straight AVERAGE and a few other variations with no joy):
Average RA = AVERAGEX( 'Reading Ages', 'Reading Ages'[RA in Months] )
Then I convert to Years Months with:
Solved! Go to Solution.
You will have to decide what to do about the month fractions.
RA Average =
var a = ADDCOLUMNS('Reading Ages',"RA",[Years]*12+COALESCE([Months],0))
var b = averagex(a,[RA])
var c = mod(b,12)
return ROUNDDOWN(b/12,0) & " years " & switch(c,1,"1 month",0,"",c & " months")
You will have to decide what to do about the month fractions.
RA Average =
var a = ADDCOLUMNS('Reading Ages',"RA",[Years]*12+COALESCE([Months],0))
var b = averagex(a,[RA])
var c = mod(b,12)
return ROUNDDOWN(b/12,0) & " years " & switch(c,1,"1 month",0,"",c & " months")
Thank you so much for taking the time to look at this. I'm learning Power BI/DAX from YouTube and here basically, so it's not been super easy - wouldn't have got as far without people like yourself.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
24 | |
9 | |
7 | |
6 | |
6 |
User | Count |
---|---|
29 | |
11 | |
11 | |
10 | |
6 |