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

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

     

5 Replies

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

         

  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support
    6 years ago

    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.