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

Issue about average excluding 0's

I have 15 pyaments and 10 are non-zero payments. I need calculate average for non-zero payments

 

Actul average = 45895.35/10   = 4589.535.

 

But when I used DAX formula as below. It counts 22 rows and gives me average for non-zero payment is 2086.15.

Please help. 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

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-sihou-msft
Moderator

Re: Issue about average excluding 0's

@JulietZhu

 

I guess your Payment_Amt may have multiple entries for same date. That's the reason why it will count 22 rows for dates.

 

In this scenario, you should build a "Total Payment_Amt" measure, then write your CountRows measure like: 

 

CountRows =
CALCULATE (
    COUNTROWS ( VALUES ( Measure[Date] ) ),
    FILTER ( VALUES ( Measure[Date] ), [Total Payment_Amt] <> 0 )
)

And you need to use Total Payment_Amt divided by CountRows. 

 

Or you can try to replace those 0s with BLANK(). Average() function will ignore empty cells automatically.

 

Regards,

View solution in original post

3 REPLIES 3
Super User
Super User

Re: Issue about average excluding 0's

You can't really do it that way. Your measure is 0 in the context of the row of your table visualization but not in just any context. You will need to probably do a SUMMARIZE and then take the average. See this design pattern here:

 

https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

 


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

Moderator v-sihou-msft
Moderator

Re: Issue about average excluding 0's

@JulietZhu

 

I guess your Payment_Amt may have multiple entries for same date. That's the reason why it will count 22 rows for dates.

 

In this scenario, you should build a "Total Payment_Amt" measure, then write your CountRows measure like: 

 

CountRows =
CALCULATE (
    COUNTROWS ( VALUES ( Measure[Date] ) ),
    FILTER ( VALUES ( Measure[Date] ), [Total Payment_Amt] <> 0 )
)

And you need to use Total Payment_Amt divided by CountRows. 

 

Or you can try to replace those 0s with BLANK(). Average() function will ignore empty cells automatically.

 

Regards,

View solution in original post

JulietZhu Member
Member

Re: Issue about average excluding 0's

 

When I just use 15 rows as data source and rowcount=10. (1st screenshot) But with my original data source, the rowcount =22 (2nd screenshot).  I checked orignal data in sql server and indeed they are 22 rows (3rd screenshot)

 

Here is my two questions.

 

1) Does powerbi sum the payment based on each day already? I think it does

 

2) I do need sum them based on each day, then filter based on sum(payment_Amt)<>0. Which DAX formula should I use? Thanks.

 

 

Capture.PNGCapture1.PNGCapture2.PNG

 

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 174 members 2,076 guests
Please welcome our newest community members: