Forum Discussion

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

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

Average Report (2).pbix27 KB

Green_Cloud

Helper I

3 years ago

Thanks so much Jihwan_Kim . It worked. I also need to find an average of conversion rate as follows. But the average is not accurate when it comes to the average conversion of of 'David' or 'Maya'. For David, it should be- 108.335 and for Maya-30.555