Calculate Averages across Measures excluding the cell values that are Blank

[vishakhawali]

Hi,

Roll	Science	Physics	Biology	Maths
1	12	14	11	23
2	13	15	12	16
3	14	16	13	0
4	15	null	14	17
5	null	17	null	17

I want to calculate the average across the columns(for each student/rollno) where Science Physics Biology Maths are my measures.

But the caveat is : if there is a blank then that doesn't go in the average.

E.g.

For roll 1 it would be (12+14+11+23)/4

For roll 5 it would be (17+17)/2

For roll 4 it would be (15+14+17)/3

For roll 3 it would be (14+16+13+0)/4

How can i do this in dax?

Also note that, when the cells contain 0 it should be counted in the average but not when it is blank(roll 3)

Please note that I would need the solution to be in DAX and not Power Query

[ryan_mayu · Accepted solution]

@vishakhawali 

here is a workaournd for you

avg = 
VAR _science=if(ISBLANK([science]),0,1)
VAR _physics=if(ISBLANK([physics]),0,1)
VAR _bio=if(ISBLANK([biology]),0,1)
VAR _math=if(ISBLANK([maths]),0,1)
return ([science]+[biology]+[maths]+[physics])/(_science+_physics+_bio+_math)

However, since I don't know what your data looks like. I think maybe we can create two measures instead of five.

measure = sum('Table'[value])
Measure 2 = if(ISFILTERED('Table'[class]),[measure],AVERAGEX(VALUES('Table'[class]),[measure]))

pls see the attachment below

[vishakhawali]

Thank you so much, this worked !! 🙂

[ryan_mayu]

you are welcome

[vishakhawali]

Hey, I cant Unpivot because I have measures and I want the solution in DAX

[jaideepnema]

Hi @vishakhawali ,

First Unpivot your table as shown below:

then create a measure like this:

Average = 

var marks=sum('Table'[Marks])

var subjectcount=CALCULATE(DISTINCTCOUNTNOBLANK('Table'[Subject]))

return DIVIDE(marks,subjectcount,BLANK())

This would give you the desired results:

Please accept this as a solution if your question has been answered !!

Appreciate a Kudos 😀