Average Rows in Matrix when some Columns are Text

[ryanjilka]

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 which are numeric and not the text columns.  Thanks fo rthe help.

[ryanjilka]

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!

[Anonymous · Accepted solution]

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

[Anonymous]

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