Forum Discussion
Calcuating average for a numerical + text mixed column
Hi, I have a sales column in which some rows have numbers but some have random strings as well. I want to eliminate the strings and only calcualte average for the numbers. I have written thi...
HI Anonymous
Download this sample PBIX file with data and measure
You can create this measure
Sales_Avg = AVERAGEX(FILTER('Sales', IFERROR(VALUE('Sales'[Sales]) >= 0,0)), VALUE('Sales'[Sales]))Regards
Phil
PhilipTreacy
Super User
Super UserHI Anonymous
Download this sample PBIX file with data and measure
You can create this measure
Sales_Avg = AVERAGEX(FILTER('Sales', IFERROR(VALUE('Sales'[Sales]) >= 0,0)), VALUE('Sales'[Sales]))
Regards
Phil
didn't work for me.
I have a column with the same problem (mixed number and text), plus I also want to filter it by a certain id, either with or without the id doesn't work!
Satisfaccion global = AVERAGEX(FILTER('Survey User Input Line', 'Survey User Input Line'[question_id.id]=571 && IFERROR(VALUE('Survey User Input Line'[suggested_answer_id.name]) >= 0,0)), VALUE('Survey User Input Line'[suggested_answer_id.name]))
OR
Satisfaccion global = AVERAGEX(FILTER('Survey User Input Line', IFERROR(VALUE('Survey User Input Line'[suggested_answer_id.name]) >= 0,0)), VALUE('Survey User Input Line'[suggested_answer_id.name]))
the error:
MdxScript(Model) (13, 96) Calculation error in measure 'Survey User Input Line'[Satisfaccion global]: DAX comparison operations do not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values.