exclude o values while calculating average

[NISHA_S]

I have this set of dataset.I want to calculate average of brokerage_perecntage_of_gross_premium where end_cert=0 .Also   exclude brokerage_perecntage_of_gross_premium <> 0.

I just calculate avg using measure...but excluding zero is not done..

Here is my measure 

CALCULATE( DIVIDE(SUM('data'[brokerage_percentage_of_gross_premium]),COUNT('data'[fact_id]),0),'data'[endt_cert]=0 )

How can i exclude o values here.....Please help?

[v-robertq-msft]

Hi, @NISHA_S 

According to your description and sample data, I think you can achieve this using the Averagex() function, you can try this measure:

Average =

AVERAGEX(FILTER(ALLSELECTED('Table'),[brokerage_percentage_of_gross_premium]<>0&&[endt_cert]=0),[brokerage_percentage_of_gross_premium])

Then place the measure into a card chart, and you can get what you want.

You can download my test pbix file below

More info about the Averagex() function in DAX

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

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

[webportal · Accepted solution]

Ok, then try this one:

Measure =
CALCULATE (

AVERAGE('data'[brokerage...]),
FILTER('data, 'data'[brokerage_percentage_of_gross_premium <>0 && 'data'[endt_cert] = 0))

[webportal]

Try this measure:

Measure =
CALCULATE (AVERAGE('data'[brokerage...]), 'data'[endt_cert] = 0)

For the calculation where endt_cert is not equal to zero just replace =0 by <> 0

[NISHA_S]

i don't want to exclude endt_cert <>0 , i need to exclude only brokerage_percentage_of_gross_premium <>0  and include only endt_cert=0