DAX measure - How to find the Average of measure

[ngct1112]

Hi all,

Here is a situation I am encountering. I would like to find the average value of 3 measures [A], [B], [C], at the same time, ignore if they are Blank().

May I know if there is any way I can handle it?

Great thanks

[amitchandak]

@ngct1112 , Try a new measure like

divide([A]+ [B]+ [C], if(isblank([A]),0,1) +if(isblank([B]),0,1) +if(isblank([C]),0,1) )

[ngct1112]

Hi @amitchandak , thanks for your reply. But is it possible I could get the sub-total of average as "1.33+3.5+4.5+5.5+9.5+9.67+11.33+13 = 58.33"?

As you may see here, the total average should be 58.33 rather than 50.67

Do you think is there any way the measure could be modified

I have uploaded a simplified example Example.pbix 

[amitchandak · Accepted solution]

@ngct1112 , Change the measure like

Average = Sumx(values(Test[ID]), divide([(A)]+ [(B)]+ [(C)], if(isblank([(A)]),0,1) +if(isblank([(B)]),0,1) +if(isblank([(C)]),0,1) ))

[ngct1112]

@amitchandak , it works. It really awesome. Great thanks