Forum Discussion

Anonymous's avatar
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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    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's avatar
    Anonymous
    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!

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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's avatar
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's avatar
    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's avatar
      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's avatar
        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?