cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

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

Thnaks!

1 ACCEPTED SOLUTION
Super User

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

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
5 REPLIES 5
Community Support

Hi @BigTommy ,

Try the solution from @edhans .

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

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

Super User

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

Helper I

Hello sir,

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

Super User

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

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Helper I

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!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors