Average excluding zero's

[Anonymous]

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

[Pr136]

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

[hamzashafiq]

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

[Anonymous · Accepted solution]

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

[mluanacruz]

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

[Anonymous]

Thanks, just needed this code

[dosansil]

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

[Anonymous]

Perfect...thx!