DAX Convert Text to Number

[MCornish]

Hi

I have a table with responses to a questionaire. In the answers column there will be a mix of text and number e.g.

Q: Enter some text    |   This is text

Q: Enter a number    |    8

What I need is a measure that does an average of the question "Q: Enter a number"

What I have so far is:

VAR Numbers = SUMMARIZECOLUMNS( Responses[Reponder], Responses[Answer], FILTER( Responses, Responses[Question] = "Q: Enter a number"), "numAnsw", SUM(Responses[Answer]))

RETURN

AVERAGEX( Numbers, [numAnsw] + 0)

Its been through a few iterations trying to get it to work which is why its a bit weird, but in every case I end up with  "Function cannot work with values of type string"

[az38 · Accepted solution]

Hi @MCornish 

try a measure

Measure = calculate(
    averagex(
        FILTER(ALL(Table2);
            NOT(ISERROR(VALUE(Table2[Answer])))
        );
    VALUE(Table2[Answer])))

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn