Forum Discussion

Anonymous

Not applicable

7 years ago

Solved

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

Anonymous

Not applicable

7 years ago

I personally try to avoid the AVERAGE function.

You can simply calculate it in the mathematical way.

sum(filtered)/count(unfiltered)

Anonymous

Not applicable

7 years ago

Anonymous so that would be this?

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

Anonymous
Not applicable
7 years ago
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
  Not applicable
  7 years ago
  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
    Not applicable
    7 years ago
    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!