Aggregating columns in each row having null values

[sbharti123]

Hi All,

I need a small help. I have a table that looks like below:

NAME	ID1	ID2	ID3
A	1	null	9
B	2	3	5
C	3	null	5

Now, I need to create a calculated column, which is average of all id columns in each row, something like below:

NAME	ID1	ID2	ID3	AVG
A	1	null	9	5
B	2	3	5	3.33
C	3	null	5	4

Is this possible to do in power bi, as I can did replace nulls with 0, but in that case my average calculations were wrong. I would be grateful if someone could guide me.

Thanks

[jthomson]

The best option would be to do some manipulation of your data so you just have name, id number and value as columns and then use a matrix, but a simple sum of each column divided by a counta of each column ought to work as well

[sbharti123 · Accepted solution]

Thanks for your quick guide. I managed to get it right as below:

Avrg =

VAR summ = (table1[id1]+ table1[id2] + table1[id3])

VAR cnt = CALCULATE(COUNTAX(table1,[id1]))+ CALCULATE(COUNTAX (table1,[id2])) + CALCULATE(COUNTAX(table1,[id3]))

return DIVIDE(summ,cnt)