Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
How would I write a formula that gives me the average sales but excludes any instances when the sales are zero.
Solved! Go to 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 ))
Proud to be a Super User!
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.
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 |
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 ))
Proud to be a Super User!
Hi @KHorseman,
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!!
Thanks, just needed this code
Good solution. I'm so grateful! Thank you very much!
Perfect...thx!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
146 | |
110 | |
107 | |
86 | |
63 |