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 ))

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

  • mluanacruz's avatar
    mluanacruz
    Helper I
    3 years ago

    Hi Anonymous,
    I am looking for something very similar, but I want the average of the "Ratio_Measure" excluding the highest and lowest number. Do you think you can help me?

    Here is a sample of my data on excel as well as the Power BI dashboard with my measures
    Here!!