Average exclude 0 with filter function

[mith_tina]

Hi,

i am new to power bi. my table is below. how can i write dax for each average attribute exclude 0?

test=average(calculate(count(value)),Filter(Attribute=A)) 

but this dax is giving me the wrong answer. how can i exclude 0?

Attribute	Value
A	1
A	0
B	1
C	1
B	0
C	0
D	1
C	3
A	2

[PhilipTreacy · Accepted solution]

Hi @mith_tina 

Download example PBIX file

Use this DAX measure

Avg Without 0 = 

VAR _tab = FILTER('Data', 'Data'[Value] <> 0)

RETURN DIVIDE(SUM(Data[Value]), COUNTROWS(_tab))

Regards

Phil

[Anonymous · Accepted solution]

Hi @mith_tina ,

I think you just need to add a filter in AVERAGEX() function.

Average Without 0 = 
AVERAGEX ( FILTER ( Data, Data[Value] <> 0 ), Data[Value] )

 Result is as below.

Best Regards,
Rico Zhou

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

[PhilipTreacy · Accepted solution]

Hi @mith_tina 

Download example PBIX file

Use this DAX measure

Avg Without 0 = 

VAR _tab = FILTER('Data', 'Data'[Value] <> 0)

RETURN DIVIDE(SUM(Data[Value]), COUNTROWS(_tab))

Regards

Phil