Forum Discussion

BigTommy's avatar
BigTommy
Helper I
6 years ago
Solved

Average witout ZERO

Hi Guys,    I have troulbe with calculating average execluting ZEROS. I have the below code which calculating me the average but is including ZEROS. Where to add <>0 in the below to exclude ZEROS p...
Need Help
Show and Tell
  • edhans's avatar
    edhans
    6 years ago

    This is the corrected measure. Always format the DAX. Makes it easy to see where parenthesis are missing BigTommy 

     

    Measure =
CALCULATE(
    AVERAGEX(
        FILTER(
            VALUES( 'Transaction History2019 2020'[Item Number] ),
            [_Average Landed Unit Cost 2019] > 0
        ),
        [_Average Landed Unit Cost 2019]
    ),
    ALL( 'Calendar'[Date] ),
    ALL( 'Transaction History2019 2020'[Number] ),
    'Calendar'[Year] = "2019"
)

     

edhans's avatar
edhans
Community Champion
6 years ago

This is the corrected measure. Always format the DAX. Makes it easy to see where parenthesis are missing BigTommy 

 

Measure =
CALCULATE(
    AVERAGEX(
        FILTER(
            VALUES( 'Transaction History2019 2020'[Item Number] ),
            [_Average Landed Unit Cost 2019] > 0
        ),
        [_Average Landed Unit Cost 2019]
    ),
    ALL( 'Calendar'[Date] ),
    ALL( 'Transaction History2019 2020'[Number] ),
    'Calendar'[Year] = "2019"
)

 

BigTommy's avatar
BigTommy
Helper I
6 years ago

THANK YOU!

 

This make sense now.

 

I have also tested another solution which replacing ZEROS with NULL in power query... also works!

 

thank you and have  great week!