Textsplit command in Power BI

[Anonymous]

Hi,

I have a list of adresses with the age of the habitants of each adres. I'm trying to create a measure in Power BI to show the average age of each adress. However, as seen below the age  is divided by a comma in a single cell. I don't want to split the cell into multiple rows or colums as some adresses consist of hundreds of habitants and i also have already 70K+ rows.

Age	Average age
29	29
22,22,22	22
22,21,21	21,33333333
22,20	21
28	28
0	0
0	0
32,31,24,23	27,5
46,45,12,11,6	24

The formula i used in Excell is the following:

=AVERAGE(VALUE(TEXTSPLIT('Age',",")))

I would like to create a measure in Power BI similar to the formula in Excell, which has the output of Average age. What would be a similair formula to achieve the same result?

Cheers

[tamerj1 · Accepted solution]

Hi @Anonymous 

please try

Average Age =
VAR String = 'Table'[Age]
VAR Items =
SUBSTITUTE ( String, ",", "|" )
VAR Length =
COALESCE ( PATHLENGTH ( Items ), 1 )
VAR T =
GENERATESERIES ( 1, Length, 1 )
RETURN
AVERAGEX ( SELECTCOLUMNS ( T, "@Item", PATHITEM ( Items, [Value] ) ), [@Item] )

[Anonymous]

Hello @tamerj1,

Thank you for your quick response, i occured one problem when putting in the formula. Which is that PowerBI does not recognize my 'Table'[Age] when i try to type it as VAR String =. Currently it is marked as text in PowerBI. What can i do to solve this.

[tamerj1]

@Anonymous 

The code was intended for a calculated column. If you wish to create a measure you need to use aggregation function like MAX, MIN or SELECTEDVALUE