Average of Measures excluding those with blank value

[Pearlsia]

Hi, 

How do I put this in DAX without having too many IFs:

I have measures like as follows:
Measure Name      Values
Measure1                 1
Measure2                 2
Measure3                 1
Measure4                 
Measure5                 0

I need to be able to get the Average which should be like below:

([Measure1] + [Measure2]+ [Measure3]  +[Measure5])/4

Denominator is 4 because we have blank for Measure4 hence excluding it.

And any of the measure may be blank.

Another scenario:
Measure Name      Values
Measure1                 
Measure2                 2
Measure3                 1
Measure4                 
Measure5                 0

I need to be able to get the Average which should be like below:

([Measure2] + [Measure3]+ [Measure5])/4

Problem is I cannot use AVERAGE function for this.

Please help and much appreciated.

🙂

[Vvelarde · Accepted solution]

@Pearlsia

Hi, using a measure with IF :

CountMeasuresNoBlank =
      IF ( [Measure1] <> BLANK (); 1 )
    + IF ( [Measure2] <> BLANK (), 1 )
    + IF ( [Measure3] <> BLANK (), 1 )
    + IF ( [Measure4] <> BLANK (), 1 )

MeasureSum = [Measure1]+[Measure2]+[Measure3]+[Measure4] 

AvgMeasures = DIVIDE([MeasureSum],[CountMeasuresNoBlank])

Regards

Victor

Lima - Peru

[Pearlsia]

Hi @Vvelarde,

Thank you heap s. With your help I was able to get the correct measure to get the average. I basically just edit my measure names and put them in variables. The CountMeasuresNoBlank is a winner! Thanks!

ScoreOverall = VAR SumScores = ([Score1]+[Score2]+[Score3]+[Score4]+[Score5)
					
	       VAR CountMeasuresNoBlank =
			IF ( [Score1] <> BLANK (), 1 )
			+ IF ( [Score2] <> BLANK (), 1 )
			+ IF ( [Score3] <> BLANK (), 1 )
			+ IF ( [Score4] <> BLANK (), 1 
			+ IF ( [Score5] <> BLANK (), 1 )) + 1
																		
	RETURN
		ROUND(DIVIDE(SumScores,CountMeasuresNoBlank),1)

@Vvelarde 

[v-sihou-msft]

@Pearlsia

In your scenario, the values are the result from measures. It's not possible to count number of measures (as your denominator) based on their results. 

Regards,