Average then Convert to Years Months - not working with slicers

duesouth — Fri, 28 Jun 2024 11:58:46 GMT

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:

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.

Re: Average then Convert to Years Months - not working with slicers

lbendlin — Sat, 29 Jun 2024 02:38:11 GMT

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

Re: Average then Convert to Years Months - not working with slicers

duesouth — Mon, 01 Jul 2024 09:22:12 GMT

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.

topic Re: Average then Convert to Years Months - not working with slicers in DAX Commands and Tips

Average then Convert to Years Months - not working with slicers

Re: Average then Convert to Years Months - not working with slicers

Re: Average then Convert to Years Months - not working with slicers