Average multiple columns with some of the column value is not numeric

[Anonymous]

Hello There

I have several columns in my table, lets say just like this below

A	B	C
1	1	1
1		0
1	-	1

I create calculation column after column C with calculate average of column A, B & C.

average of first row is 100%, since the calculation is (1+1+1)/3 which is correct

average of second row is 50% because even second row of column B is blank, by using regular average it will ignore blank cell, so the calculation is (1+blank+1)/2 which also correct

average of third row is error, since value on column B is "-" which is non numerical

i've tried using iferror syntax which convert "-" become 0, but then the calculation become 66.67% because the calculation will be like this (1+0+1)/3, I want the result is 100% because the calculation should be like (1+1)/2, ignoring the "-"

Any help?

thanks a lot

[FreemanZ]

hi @Anonymous 

Suggest to replace "-" with null in Power Query Editor firstly.

[Anonymous]

Can't do it, because it's source is live document, each time PBI service is refreshing, that "-" thing is keep re-appear.

"-" mark is required by the owner of data for their routine operation

[v-jingzhang · Accepted solution]

Hi @Anonymous 

I assume that all three columns are of Text type and "-" may appear in any of them, you can try below code to create a calcualted column.  

Column = 
var a=IFERROR(VALUE([A]),BLANK())+IFERROR(VALUE([B]),BLANK())+IFERROR(VALUE([C]),BLANK())
var b=ISNUMBER(IFERROR(VALUE([A]),BLANK()))+ISNUMBER(IFERROR(VALUE([B]),BLANK()))++ISNUMBER(IFERROR(VALUE([C]),BLANK()))
return
DIVIDE(a,b)

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Anonymous]

Exactly what i need, thank you