Forum Discussion
Average exclude 0 with filter function
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 |
Hi mith_tina
Use this DAX measure
Avg Without 0 = VAR _tab = FILTER('Data', 'Data'[Value] <> 0) RETURN DIVIDE(SUM(Data[Value]), COUNTROWS(_tab))Regards
Phil
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi mith_tina
Use this DAX measure
Avg Without 0 = VAR _tab = FILTER('Data', 'Data'[Value] <> 0) RETURN DIVIDE(SUM(Data[Value]), COUNTROWS(_tab))Regards
Phil
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.