Forum Discussion
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.
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 ))
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])))