Forum Discussion

kvandehy's avatar
kvandehy
New Member
9 years ago
Solved

DAX | Average If Value > 0

Please help - I am very new to DAX functions and have not been able to find a solution to my problem.

 

I have a column of hours worked for each employee and I want to create a measure to calculate the average hours per employee BUT there are some who have zero hours for various reasons and I do not want those employees included in the average (don't want them added to the count when dividing). In excel I would easily do this with and AVERAGEIF function but I cannot for the life of me figure out how to calculate it in PowerBI.

 

Thanks!

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    9 years ago

    If you wanted to literally average the values, it would be

     = CALCULATE ( AVERAGE(Employee[Hours]), Employee[Hours] <> 0 )

     

    Though, I can totally imagine other interpretations of "average" :)

3 Replies

  • prathy's avatar
    prathy
    Advocate III
    9 years ago

    Hi,

     

    Are you after something like below? if yes, then you can create a measure like this - AVG = SUM(Table2[hours])/CALCULATE(COUNT(Table2[date]),ALLNOBLANKROW(Table2[hours]))

     

    • kvandehy's avatar
      kvandehy
      New Member
      9 years ago

      No, unfortunately the employees with no hours have "0.00" in the Charge Hours column so it is not blank. I would like to be able to find average hours for all employees for the month by service unit, department and total company but the 0's are throwing off the count of employees.

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        If you wanted to literally average the values, it would be

         = CALCULATE ( AVERAGE(Employee[Hours]), Employee[Hours] <> 0 )

         

        Though, I can totally imagine other interpretations of "average" :)