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

     

amitchandak's avatar
amitchandak
Super User
6 years ago

BigTommy , Try

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

 

or

 

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

BigTommy's avatar
BigTommy
Helper I
6 years ago

Hello sir, 

 

I have entered the code buit the last part (line4) has some errors... 

 

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