Hi Expert,
How can i take the average of value where i have NA (a text value) present in the column.
I cannot exclude this value as it is coming from a separate file and part of the data which i received to make a report.
NA- Not Applicable
where 1 is lowest and 3 is highest.
If i change the data type to value than NA turns into error and error i have to replace with any number that will effect my average.0 i cannot use as some values will be 0 which means score not given.
What is the workaround to get the average score for each attribute.
You can’t take an average with a text in the column you want to average. I would delete that from your data or go back to the source and have it corrected. If its a certain category then filter it out before loading it into your power bi report. I would also put in a category Not Applicable as a selection for your user to choose.
Hi@keith
Thank you for your reply.
The issue is some of the atteibutes are are NA where check does not check bcz its available in the class. However they are using the general form.
If you want to replace it NA which value you will give as 0(zero) is already used.
Can we change NA to 99 and before calculating average we can filter out 99 values?
well you are going have to make that determination if has a value or not. If it doesn’t then delete it before your load that has NA into the model. If it needs a value then you need to manual change from NA to 1 to 3 ranking. You can filter that out in your power query activity.
What i would do filter all the NA together and sort by Attribute. Determine if that attribute is need and then do a find and replace activity. If that attribute is not needed then delete the rows before close and apply.
