topic Re: Average multiple columns with some of the column value is not numeric in DAX Commands and Tips

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

Anonymous — Fri, 14 Apr 2023 06:54:46 GMT

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

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

FreemanZ — Fri, 14 Apr 2023 07:14:16 GMT

hi Anonymous

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

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

Anonymous — Fri, 14 Apr 2023 13:21:17 GMT

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

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

v-jingzhang — Wed, 19 Apr 2023 03:34:42 GMT

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.

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

Anonymous — Thu, 20 Apr 2023 01:36:55 GMT

Exactly what i need, thank you