Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
68 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |