Forum Discussion
How to calculate average only for certain values?
So I have inherited from another user a table with a couple of columns that calculate the difference between two other columns and then replaces all negative values with zero, as follows: Column ...
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 Don't worry is a simple Measure, just keep studying DAX because is amazing.
CALCULATE(AVERAGE(Table[Column D]);FILTER(Table,[Column D]>0))
Good Luck!
Anonymous thanks so much for replying, but that is throwing an error saying "The syntax for ';' is incorrect."
Any idea why that might be?