Average witout ZERO

[BigTommy]

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

_Landed Ave Product Unit Cost 2019 ALL() =

CALCULATE ( AVERAGEX ( VALUES ( 'Transaction History2019 2020'[Item Number] ), [_Average Landed Unit Cost 2019] ),

ALL ( 'Calendar'[Date] ),

ALL ( 'Transaction History2019 2020'[Number] ), 'Calendar'[Year] = "2019" )

in case this is important the [_Average Landed Unit Cost 2019] is a measure. See below:

_Average Landed Unit Cost 2019 =

CALCULATE (AVERAGE ( 'Transaction History2019 2020'[Landed Unit Cost] ),

ALL ( 'Transaction History2019 2020'[Number] ))

the [Landed Unit Cost] is the table where I have values and zeros

So PBI is calculating 0.467+0.442048835+0.442+0.442 = 1.793048835 / 20 =0.08965

and the average should be 1.793048835/4=0.44826220875 

PLEASE HELP!

Thnaks!

[V-lianl-msft]

Hi @BigTommy ,

Try the solution from @edhans .

If the problem persists,could you share a PBIX file with dummy data? 

Please mask any sensitive data before uploading.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

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

Hello sir, 

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

[edhans · Accepted solution]

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]

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!