Forum Discussion
How to calculate average only for certain values?
Of what I can make from you starting text the measure should be something like this:
DIVIDE(
SUM('Table'[Column D]),
CALCULATE(COUNT('Table'[Column D]),FILTER(ALL(Table);'Table'[Column D] >0))
)UPDATE! SUCCESS!
Anonymous you got me VERY close, the solution ended up being what you gave me, minus the "All" in the filter:
= DIVIDE(SUM('Table'[Column D]),CALCULATE(COUNT('Table'[Column D]),FILTER(Table,'Table'[Column D]>0)))
I think the "All" was negating the filter somehow.
Anyway, big thanks to you and Anonymous! Problem Solved!
Anonymous we're getting so close! that expression ran when I changed it to:
= DIVIDE(SUM('Table'[Column D]),CALCULATE(COUNT('Table'[Column D]),FILTER(ALL(Table),'Table'[Column D]>0)))
but it bizarrely dropped the average way down which would suggest to me it has somehow increased the count of values being averaged. Any ideas?
Anonymous Correction: I confirmed the COUNT function executed correctly and came up with the correct count of values by just running the COUNT function separately. I then ran the SUM function separately and found that the sum is undercalculated by about 966,000....how is this possible? I dumped Column D into Excel and calculated the sum myself and it should be 988,636 but Power BI is somehow only coming up with 22,216...what gives?