Forum Discussion
Average excluding zero's
Hi,
I want to calculate cost/hour excluding zeros by just dividing "total_cost" with "Average usage by unit" where Average usage 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 |
var _totalcost = sum(tablename[total_cost])
Var _unitExcludingNull = IF([Average Usage by Unitreturn] > 0 && Average Usage by Unit <> Blank(),Average Usage by Unit,Blank())Divide( _totalcost,_unitExcludingNull,0)
5 Replies
Expected output ,Didn't understand your scenario
Hi mh2587 The numbers in Total row are the one I am expecting. The total cost/hour should be dividing the total_cost with Average Usage by Unit column excluding the cases where Average Usage by Unit is 0 or null.
var _totalcost = sum(tablename[total_cost])
Var _unitExcludingNull = calculate(sum(tablename[Average Usage by Unit]),tablename[Average Usage by Unit]<>Blank())
returnDivide( _totalcost,_unitExcludingNull,0)
Can't aggregate the Average Usage by Unit as it's already a calculated measure.
var _totalcost = sum(tablename[total_cost])
Var _unitExcludingNull = IF([Average Usage by Unitreturn] > 0 && Average Usage by Unit <> Blank(),Average Usage by Unit,Blank())Divide( _totalcost,_unitExcludingNull,0)
