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.
Hi all,
Im calculating an average monthly sales for the year in a measure but I want my measure to return zero if there are amounts in the month that contain zero. i.e., not calculate at all if there are any months that contain zero.
Solved! Go to Solution.
Hi @Anonymous
try this, create the measure
Monthly Average in a year =
var _t=FILTER(ALL('Table'),'Table'[Month sales]<>0 && LEFT('Table'[Column1],2)=LEFT(MIN('Table'[Column1]),2))
var _countYear=CALCULATE(COUNT('Table'[Month sales]),_t)
var _average=CALCULATE(AVERAGE('Table'[Month sales]),_t)
return IF(_countYear<>12,BLANK(),_average)
result
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
try this, create the measure
Monthly Average in a year =
var _t=FILTER(ALL('Table'),'Table'[Month sales]<>0 && LEFT('Table'[Column1],2)=LEFT(MIN('Table'[Column1]),2))
var _countYear=CALCULATE(COUNT('Table'[Month sales]),_t)
var _average=CALCULATE(AVERAGE('Table'[Month sales]),_t)
return IF(_countYear<>12,BLANK(),_average)
result
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , Not clear what do mean by containing 0.
If you want handle blank we usually add +0 to the measure calculation
The information you have provided is not making the problem clear to me. Can you please explain with an example.
Appreciate your Kudos.
Hi thanks for the quick reply,
so the monthly average in a year is the measure I want to create. YOu can see the value "6998" is the monthly average of the whole year of 2021. Therefore, I want the measure to return blank jan-20 to dec-20 because the months from jan-mar 20 has no sales.
@Anonymous , try a measure like
Var _1= calculate(distinctCOUNT(Table[Mon year]), filter(allselected(Table), Table[Year] = max(Table[Year])))
return
if(_1<12, blank(), [monthly average in a year])
i think we are almost there.. the first part of the formula returns 12 for all months. i think we need a part of the formula that says if there is data, then count the rows else do not count.
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 |
---|---|
106 | |
105 | |
79 | |
69 | |
62 |
User | Count |
---|---|
142 | |
105 | |
103 | |
85 | |
70 |