Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
duesouth
Helper I
Helper I

Average then Convert to Years Months - not working with slicers

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

IDYearsMonthsRA in MonthsTest
18298Autumn
2147175Autumn
3135161Autumn
417 204Autumn
5135161Autumn
617 204Autumn
7138164Autumn
8147175Autumn
917 204Autumn
1014 168Autumn
16375Spring
2105125Spring
317 204Spring
4103123Spring
5152182Spring
617 204Spring
710 120Spring
817 204Spring
9132158Spring
10121145Spring

 

Pupil Data

IDForenameSurnameFree School Meals
1PupilOneNo
2PupilTwoNo
3PupilThreeNo
4PupilFourNo
5PupilFiveYes
6PupilSixNo
7PupilSevenNo
8PupilEightYes
9PupilNineNo
10PupilTenYes

 

Classes

IDClass
17French1
27French1
37French1
47French1
57French1
67French2
77French2
87French2
97French2
107French2
17English1
27English2
37English1
47English2
57English1
67English1
77English2
87English1
97English2
107English1
17Science2
27Science1
37Science2
47Science1
57Science2
67Science2
77Science1
87Science2
97Science1
107Science1

 

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:

 

Reading Age Averaged =
VAR year1 = INT('Reading Ages'[Average RA] / 12)
VAR month1 = 'Reading Ages'[Average RA] - year1 * 12
RETURN
    SWITCH(
        TRUE(),
        'Reading Ages'[Average RA] < 12, "0 Year " & FORMAT('Reading Ages'[Average RA], "0.0") & " Months",
        'Reading Ages'[Average RA] = 12, "1 Year 0 Months",
        year1 & " Years " & FORMAT(month1, "0.0") & " Months"
    )
 
Any help greatly appreciated.
1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

lbendlin_0-1719628649650.png

 

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")

 

View solution in original post

2 REPLIES 2
lbendlin
Super User
Super User

lbendlin_0-1719628649650.png

 

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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.