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
It is a little more complecated than "Yes" and "No" and so this doesn't seem to work. I only want to average the numerics and not the text but I can't because of the type of field. Is there another way to acomplish this? Thanks!
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