Forum Discussion
Solved
Average Rows in Matrix when some Columns are Text
I am trying to average rows in my Power Bi matrix but am unable to because my values field contains values that are both text and numeric. Is there a way to handle this? Average only the columns wh...
Hi ryanjilka ,
Then you could try ISTEXT() function.
https://docs.microsoft.com/en-us/dax/istext-function-dax
Modify the formula as below.
_value = IF(ISTEXT(SELECTEDVALUE('Table'[value])),0,SELECTEDVALUE('Table'[value])) Measure = AVERAGEX(ALL('Table'[id]),[_value])Or ISERROR() function.
https://docs.microsoft.com/en-us/dax/iserror-function-dax
_value = IF(ISERROR(SELECTEDVALUE('Table'[value])+0),0,SELECTEDVALUE('Table'[value])+0) Measure = AVERAGEX(ALL('Table'[id]),[_value])Best Regards,
Jay
Hi ryanjilka ,
Depends on how complicate your column is. If the column only contains "yes" or "no" and the numeric, you could create a measure to replace the value.
Sample data for your reference.
_value = IF(SELECTEDVALUE('Table'[value])="yes"||SELECTEDVALUE('Table'[value])="no",0,SELECTEDVALUE('Table'[value])+0)
Measure = AVERAGEX(ALL('Table'[id]),[_value])
Best Regards,
Jay