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,

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