Forum Discussion

JulietZhu's avatar
JulietZhu
Helper IV
8 years ago
Solved

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 ro...
  • v-sihou-msft's avatar
    v-sihou-msft
    8 years ago

    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,

v-sihou-msft's avatar
v-sihou-msft
Microsoft Employee
8 years ago

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,

  • JulietZhu's avatar
    JulietZhu
    Helper IV
    8 years ago

     

    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.