Forum Discussion

mith_tina's avatar
mith_tina
Frequent Visitor
4 years ago
Solved

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 th...
Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    4 years ago

    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's avatar
    Anonymous
    4 years ago

    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's avatar
PhilipTreacy
Icon for Super User rankSuper User
4 years ago

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