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 wrote:Anonymous thanks so much for replying, but that is throwing an error saying "The syntax for ';' is incorrect."
Any idea why that might be?
Change the ';' into ',' might depend on region setting which one you need to use.
Anonymous That resolved the syntax error, but did not change the result of the calculation. The average should go way up with all the zeroes taken out, should it not?
I personally try to avoid the AVERAGE function.
You can simply calculate it in the mathematical way.
sum(filtered)/count(unfiltered)
Anonymous so that would be this?
CALCULATE(DIVIDE,(SUM(FILTER(Table,[Column D]>0))),(COUNT(FILTER(Table,[Column D]>0))))
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))
)