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 ))
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.
| model | unit | Average Usage by Unit | total_cost | cost/hour |
| m1 | s1 | $157.34 | $0.00 | |
| m1 | s2 | $1,603.29 | $0.00 | |
| m1 | s3 | $35.99 | $0.00 | |
| m1 | s4 | $589.13 | $0.00 | |
| m1 | s5 | $719.70 | $0.00 | |
| m1 | s6 | 20 | $2,657.60 | $132.88 |
| Total | 20 | $2,657.60 | $132.88 |