cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JulietZhu Member
Member

Average excluding zero's

I have 15 payments and I need calcualte the average for non-zero payment.

 

Actually there are 10 non-zero payment and average =45895.35/10  =4589.535.

 

But when I used the formula as below. It counts 22 and give me aveage of 2086.15.  Please help me what is wrong with it. Thanks.

 

Avg_NonZero_Payment_Amt = CALCULATE(AVERAGE(Measure]),FILTER(Measure,Measure[Payment_Amt]<>0))

CountRows = Calculate(countrows(Measure), FILTER (Measure,Measure[Payment_Amt]<>0))

 

  Capture.PNG

5 REPLIES 5
Super User III
Super User III

Re: Average excluding zero's

Hi @JulietZhu,

 

Try the following measure:

 

Avg_NonZero_Payment_Amt =
CALCULATE ( AVERAGE ( [Measure] ), SUM ( Measure[Payment_Amt] ) <> 0 )

Regards,

MFelix


Regards

Miguel Felix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




pxg08680 Member
Member

Re: Average excluding zero's

@JulietZhu

Average = CALCULATE(AVERAGE(Table1[Payment_Amt]),FILTER(Table1,Table1[Payment_Amt] <> 0))

 

Count = CALCULATE(COUNT(Table1[Payment_Amt]),FILTER(Table1,Table1[Payment_Amt] <> 0))

a1.PNG I

I used the same data provided by you and got correctly. Try the above steps again.

JulietZhu Member
Member

Re: Average excluding zero's

Hi @MFelix, I tried, but got syntax error.

 

--------------------------------------------------------------------------------------

 

Try the following measure:

 

Avg_NonZero_Payment_Amt =
CALCULATE ( AVERAGE ( [Measure] ), SUM ( Measure[Payment_Amt] ) <> 0 )

Regards,

MFelix

 
Microsoft v-jiascu-msft
Microsoft

Re: Average excluding zero's

Hi @JulietZhu,

 

Did you try the solution of @pxg08680

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Microsoft v-jiascu-msft
Microsoft

Re: Average excluding zero's

Hi @JulietZhu,

 

Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

 

Best Regards!
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors