Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sglee
Frequent Visitor

Average of a range of data and exclude zero

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

Capture.JPG

 

Any formula that I can use to get the answer?

 

Thank you.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

@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. 

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

@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.

@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
Not applicable

@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. 

@Anonymous Thank you! The solution work.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors