Forum Discussion

Green_Cloud's avatar
Green_Cloud
Helper I
3 years ago
Solved

Calculating Average while ignoring zero/null values

Hi All,   I need to find out average based on the following dataset. But I just need to show the available month's value in the average when one of the month's value is null or zero. The formual I ...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    Thank you for your feedback.

    Could you please check the below measure and the attached file, whether it suits your requirement?

     

    Average (February & March) = 
(
    (
        CALCULATE (
            SUM ( [Working Hour] ),
            FILTER ( 'Sample', 'Sample'[Month] = "February" )
        )
    )
        + (
            CALCULATE (
                SUM ( [Working Hour] ),
                FILTER ( 'Sample', 'Sample'[Month] = "March" )
            )
        )
)
    / COUNTROWS (
        FILTER (
            'Sample',
            'Sample'[Month]
                IN { "February", "March" }
                && 'Sample'[Working Hour] <> 0
        )
    )
Jihwan_Kim's avatar
Jihwan_Kim
Super User
3 years ago

Hi,

Thank you for your feedback.

Could you please check the below measure and the attached file, whether it suits your requirement?

 

Average (February & March) = 
(
    (
        CALCULATE (
            SUM ( [Working Hour] ),
            FILTER ( 'Sample', 'Sample'[Month] = "February" )
        )
    )
        + (
            CALCULATE (
                SUM ( [Working Hour] ),
                FILTER ( 'Sample', 'Sample'[Month] = "March" )
            )
        )
)
    / COUNTROWS (
        FILTER (
            'Sample',
            'Sample'[Month]
                IN { "February", "March" }
                && 'Sample'[Working Hour] <> 0
        )
    )
    • Green_Cloud's avatar
      Green_Cloud
      Helper I
      3 years ago

      It should ignore the 0% conversion rate for John and Alex. It is considering that.