Average Issue

[vlassauw]

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

[v-chuncz-msft]

@vlassauw,

Here you need to use a measure.

Measure =
DIVIDE ( SUM ( Table1[HoursSick] ), SUM ( Table1[HoursWork] ) )

[vlassauw]

Thank you for responding but it doesn't work, as in Power BI the measure needs to be aggregated,

If you choose Average, it will still take the 0% rows into account. Other aggregation types, as far as I can see are pointless

If you choose Don't Summarize my report is ungrouped which is not an option.

[jthomson · Accepted solution]

That 25.71% looks to be an average of the individual percentages - it's nothing to do with taking into account (or otherwise) the rows where nobody's sick. The original measure posted in reply should work absolutely fine, and I'm thinking it's something to do with how the visual is interpreting the totals row

edit - just put in the data you listed and the measure listed and it works correctly out of the box:

[vlassauw]

Somehow it refreshed wrong when I tried your formula, so i tried again and it works, thank you!!!!

[vlassauw]

The mistake I made was I applied the formula to a column in stead of a measure