Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago
Solved

Average excluding zero's

How would I write a formula that gives me the average sales but excludes any instances when the sales are zero.

  • Anonymous's avatar
    Anonymous
    10 years ago

    Assuming this is a simple average of rows of sale data, and not an average of another measure that aggregates sales in some other way...

     

    Avg Sales= CALCULATE( AVERAGE(TableName[SaleAmount]), FILTER(TableName, TableName[SaleAmount] <> 0 ))

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago

    Assuming this is a simple average of rows of sale data, and not an average of another measure that aggregates sales in some other way...

     

    Avg Sales= CALCULATE( AVERAGE(TableName[SaleAmount]), FILTER(TableName, TableName[SaleAmount] <> 0 ))

    • Anonymous's avatar
      Anonymous
      Not applicable
      10 years ago

      Perfect...thx!

    • dosansil's avatar
      dosansil
      Helper I
      8 years ago

      Good solution. I'm so grateful! Thank you very much!

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Thanks, just needed this code

  • hamzashafiq's avatar
    hamzashafiq
    Kudo Collector
    4 years ago

    Hey,

     

    I have similar issue, I want to calculate the "cost/hour" excluding zeros with just dividing "total_cost" with "Average usage by unit" where "Average usage by unit" is a measure. 

     

    modelunitAverage Usage by Unittotal_costcost/hour
    m1s1 $157.34$0.00
    m1s2 $1,603.29$0.00
    m1s3 $35.99$0.00
    m1s4 $589.13$0.00
    m1s5 $719.70$0.00
    m1s620$2,657.60$132.88
         
    Total 20$2,657.60$132.88
  • Pr136's avatar
    Pr136
    Regular Visitor
    3 years ago

     If we use FILTER in the Formula, it could filter out the dimensions which has values as 0. If we want to show all the dimensions but the average to be calculated based on only the non-zero values, then use something like this. 

    AVG-Excluding zero =CALCULATE(AVERAGEX(Mock_Data_For_Paginated_Report_Demo,Mock_Data_For_Paginated_Report_Demo[Overall Rating - Int]),FILTER(ALL(Mock_Data_For_Paginated_Report_Demo),Mock_Data_For_Paginated_Report_Demo[Overall Rating - Int]=Max(Mock_Data_For_Paginated_Report_Demo[Overall Rating - Int])))