Forum Discussion
Textsplit command in Power BI
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
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] )
3 Replies
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] )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.
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