How to calculate average only for certain values?

[Anonymous]

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!

[Anonymous · Accepted solution]

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))
)

[Anonymous]

@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]

@Anonymous thanks so much for replying, but that is throwing an error saying "The syntax for ';' is incorrect."

Any idea why that might be?

[Anonymous]

---

@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]

@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?

[Anonymous]

I personally try to avoid the AVERAGE function.

You can simply calculate it in the mathematical way. 

sum(filtered)/count(unfiltered)

[Anonymous]

@Anonymous so that would be this?

CALCULATE(DIVIDE,(SUM(FILTER(Table,[Column D]>0))),(COUNT(FILTER(Table,[Column D]>0))))

[Anonymous · Accepted solution]

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))
)

[Anonymous]

@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 · Accepted solution]

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]

@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?