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,

Roseventura's avatar
Roseventura
Responsive Resident
5 years ago

Thank you v-sihou-msft!  I was having this same issue and this solution was exactly what I was looking for!