Average of a range of data and exclude zero

[sglee]

How to get average based on the criteria but exclude those criteria with zero value?

For example:

Row 1 = Sum(Criteria 1:Criteria 10)/ No of criteria with value (10)

Row 2 = Sum(Criteria 1:Criteria 10)/ No of criteria with value (5)

Overall = Sum of all criteria

Any formula that I can use to get the answer?

Thank you.

[Anonymous]

@sglee Please go to edit query
select all the columns that needs to be considered in average calculation
click on add column tab

Click on statistics and select average from dropdown.

If this post helps accept as solution.

[sglee]

@Anonymous By doing this, did not exclude the zero value criteria.

For Example Criteria 1 = 4 Criteria 2 = 4 Criteria 3 = 0

What i want is Average = (4+4)/2 = 4

Result i get is Average = (4+4)/3 = 2.67

[Anonymous · Accepted solution]

@sglee At first I thought you have blank values instead of zero. One quick solution is to replace all zero with blank

Or else create a measure 

Measure = 
VAR _sum = SUMX('Table','Table'[Criteria1]+'Table'[Criteria2]+'Table'[Criteria3]+'Table'[Criteria4])
VAR _count = SUMX('Table',CALCULATE(COUNT('Table'[Criteria1]),'Table'[Criteria1]>0)+CALCULATE(COUNT('Table'[Criteria2]),'Table'[Criteria2]>0)+CALCULATE(COUNT('Table'[Criteria3]),'Table'[Criteria3]>0)+CALCULATE(COUNT('Table'[Criteria4]),'Table'[Criteria4]>0))
RETURN DIVIDE(_sum,_count,0)

here  I included only four columns. Please include other columns as well. 

[sglee]

@Anonymous Thank you! The solution work.