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 C = datediff('Table'[Column B].[Date],'Table'[Column A].[Date],DAY))
Column D = if('Table'[Column C]>0,'Table'[Column C],0)
There is then a visual which displays the average of Column D via the following Measure:
Measure = CALCULATE(AVERAGE(Table[Column D]))
The problem with this is that the zeroes in Column D are artificially pulling the average down (Column D is a "days late" kind of metric, so we shouldn't be including zeroes). How can I go about changing the measure to only calculate the average for non-zero values? I tried using FILTER and WHERE statements as follows with no success (syntax errors):
Measure = WHERE(Table[Column D]>0),CALCULATE(AVERAGE(Table[Column D]))
Measure = CALCULATE(AVERAGE(FILTER(Table,[Column D]>0)))
It is probably pretty obvious from my question that I am a complete newbie to DAX syntax. Could someone please help? I'm sure there's probably a MUCH simpler way of accomplishing this task, I just don't know the language to do it. Thanks in advance!
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!