Calculating Average while ignoring zero/null values

[Green_Cloud]

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 used for average is: 

Two Month's Average = ((CALCULATE(SUM([Working Hour]), FILTER('Sample','Sample'[Month]="February"))) + (CALCULATE(SUM([Working Hour]), FILTER('Sample','Sample'[Month]="March"))))/2

Dataset:

I have the current output as follows. But it is inaccurate when the filtered value is either 'Alex' or 'John' as they have zero working hours in one of the months. I need a dax which can ignore null/zero values. The desired average value for 'Alex' should be 4 and for 'John', it should be 3. 

Below is the Power BI file link-

https://drive.google.com/file/d/1vRA6h82VIT7zvvtFczW3eJnr-gxXC-z2/view?usp=share_link

Thanks

[Green_Cloud]

edited

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but please try something like below.

Please check the attached pbix file.

Avg measure: = 
CALCULATE (
    AVERAGE ( 'Sample'[Working Hour] ),
    FILTER ( 'Sample', 'Sample'[Working Hour] <> 0 )
)

[Green_Cloud]

@Jihwan_Kim , Thanks so much for your solution! It's close but I needed to make a slight change in the dataset. I needed to filter out the data only for February and March to calculate average. Attached is the link with updated file-

https://drive.google.com/file/d/1vRA6h82VIT7zvvtFczW3eJnr-gxXC-z2/view?usp=share_link

[Jihwan_Kim · Accepted solution]

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]

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

Below is the updated PBI file-
https://drive.google.com/file/d/1MPwUn89yxiUYwSWpJKispl7LesBJQd9Y/view?usp=share_link

The Dataset-

[Jihwan_Kim]

Hi,

Please check the attached file whether it suits your requirement.

Thanks.

[Green_Cloud]

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