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 have a column of data that contains responses to a questionnaire. For some of the questions, the responses are text values (Y, N, N/A), and for others the responses are a numeric scale of 0-10. Here is an example:
Question 1 Y
Question 2 6
Question 3 8
Question 4 N
Question 5 10
Question 6 N/A
I would like to calculate the average value of the numerical scores while ignoring the text responses; in this example, the average would be 8. In Excel, if the column of values is defined at General format I can use the AVERAGE function and it works perfectly. When I try this in Power BI, it fails.
I went into Power Query to ensure the data type for the column is General format; i.e. the type symbol on the left side of the column header is the stacked "ABC 123", the text values are displayed as left-justified, and the numeric values are right-justified. But, when I use "Close & Apply" to close out the editor and then look at the data tables in the model, the column has been changed to Text only; i.e. all values are left-justified. When I create a measure over this column using the AVERAGE function it returns the error "The function AVERAGE cannot work with values of type String". When I try using AVERAGEA (which handles text values) there isn't an error but the result is always zero, which confirms to me that all of the values have been converted to text, even though they were in General (mixed text and numeric) format in Power Query.
The only solution I have found is to create a calculated column that checks the contents of the original column and writes out the numeric values. It's a messy solution, but it works. Why is the data type changing? And is there a better solution?
Solved! Go to Solution.
Hi @mjbernier ,
Sorry for the confusion, I read it incorrectly your question.
Create the following measure:
Measure = AVERAGEX ( FILTER ( ADDCOLUMNS ( GROUPBY ( ALLSELECTED ( Table1 ); Table1[Question]; Table1[Answer] ); "Number_Answer"; IFERROR ( Table1[Answer] + 0; 0 ) ); [Number_Answer] <> 0 ); [Number_Answer] )
Should give expected result.
Once again sorry for the incorrect information previouly.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @mjbernier ,
The ABC 123 on the top of the column symbols that you have text and numbers on a column, by default is what is uploaded in some columns. When you go to the aplly this it's read as text and not as numbers.
Go to the query editor click on the ABC123 and select the number format for the column with the numerics, then close and apply and your data will be as number.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @mjbernier ,
Sorry for the confusion, I read it incorrectly your question.
Create the following measure:
Measure = AVERAGEX ( FILTER ( ADDCOLUMNS ( GROUPBY ( ALLSELECTED ( Table1 ); Table1[Question]; Table1[Answer] ); "Number_Answer"; IFERROR ( Table1[Answer] + 0; 0 ) ); [Number_Answer] <> 0 ); [Number_Answer] )
Should give expected result.
Once again sorry for the incorrect information previouly.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering 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 |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |