[sims] Reading Ages Power BI

[duesouth]

We have Reading Ages in SIMS - in the format 11/2. I'm trying to get them into Power BI so I can then have an average and then various slicers on the page that change the average.

 

I've managed to convert to years/months by converting to months in Power Query and then this formula:

 

Reading Age =

 

var year1= INT('Reading Ages'[RA in Months]/12)

var month1='Reading Ages'[RA in Months]- year1*12

return

SWITCH( TRUE(),

'Reading Ages'[RA in Months]<12,"0 Year "& 'Reading Ages'[RA in Months] & " Months",

'Reading Ages'[RA in Months]=12,"1 Year "& "0 Months",

year1& " Years " & month1 & " Months")

 

 

The trouble is, when I try to average this Reading Age formula, the card will just give me First, Last, Count and Count Distinct options.

 

Has anyone had any success with Reading Ages in Power BI? If so, how did you go about converting the SIMS format to one that Power BI can average? Thanks in advance for any help.

[RootsSchImp]

HI @duesouth,

 

If you create a measure Avg reading age = AVERAGE('reading ages'[RA in Months])

 

Then create another measure using exactly the same code from your reading age calc column but with [Avg reading age] instead of the column reference then you'll have a slicer responsive age expressed in years and months

[duesouth]

Hi @RootsSchImp - worked a treat. Can't thank you enough!