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

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

7 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

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

     

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