Forum Discussion
Cannot apply VALUE on FILTERed table
You cannot mix data types in the same column like you do with your Answer. So it will default to text in this case, and you cannot average text. Easiest thing to do is in Power Query, Reference the Main query, but filter down to just Numerical and then a simple average function over that answer column after you change the data type to a numerical one. I guess you could create a new column in the same table to only give a number, but why make it more complicated then it needs be?
Hello Nick,
I understand that you can't mix data type.
But one should be able to convert the rows of a filtered table only right?
I don't want to change the query because, I will need to realize different measures from the non-numerical answers in the future.
And then I would have to change the query again to filter what I want.. it's unpractical.
I could create a column and convert non-numerical answer to numerical answer by changing the scale, but I want to avoid touching the data as much as possible here.
I want to encapsulate the whole complexity of this operation (which is not very complex actually.. ) in the DAX measure.
I dont think you can change the data type via a measure. But what if you add the following as a custom column to your query?
Then just a measure of:
Avg of Numerical = AVERAGE(Table1[Custom] )