Average Issue

The Power BI Query

In my Power BI query I have a Department and an Employee level

I have a measure called HoursWork and measure called HoursSick

I need percentage hours sick on hours worked

In the query I simply divide sick by worked and I get a correct percentage of course on the lowest level Employee

Some Employees have no HoursSick so the percentage there is 0

Query output Example:

DEP      Employee    HoursWork    HoursSick  Sick%

1                 1                  152               0              0%

1                 2                  128           120            93.75%

1                 3                    88               8              9.09%

1                 4                   112              0              0%

The Power BI Report

The report is on Department level showing

DEP        HoursWork     HoursSick      Sick%

1                   480               128           25.71%

The percentage is wrong as it takes the 0% rows into account as well

I want the percentage to be the  result of total hourssick divided by hourswork, meaning 128/480 = 26.67%

Because of this issue the column total (over all departments) is way off even more.

Anyone?

Thanks,

Vincent