Trying to get the average from a column that has some text fields in

[KA95]

Hi there,

I know it sounds silly, but basically I have a column called "result-value" and this column has many different types of values, from numbers to text. The result value's have different types of question sets, some questions sets have no text fields, so I'm completely filtering out the text fields on my report with the filter pane. 

So for example "Question Set 1" (filtered above) is purely just numerical values. However, I can't get the average value of results because the column is obviously a text column. 

So I created this measrue below:

Average Score = CALCULATE(SUM('Quiz Results'[result_value]), FILTER('Quiz Steps', 'Quiz Steps'[step_name] = "Question Set 1"))

Now this doesn't work, and it's obvious why because again the column in question is a text column. Is there any way this can be acheived? Either from a measure or power query? I don't want to delete all the text fields in "result_value" because I need the text fields in there, but I want to be able to make proper use of the numerical fields. Any help would be appreciated. 

[KA95 · Accepted solution]

Managed to find a way that fixed this.

I went into Query Editor, added a new custom column with the following function:

Text.Remove([String],{"A".."z"})

This removed all string values in the new custom column so I had a new results column with just integer values.