PowerBI and filtered sum calculation

Question

I should be able to make a report concerning a relationship between sick leaves (days) and man-years. Data is on monthly level, consists of four years and looks like this (there is also own columns for year and business unit):

Month   Sick leaves (days)  Man-years
January        35               1,5
February        0               1,63
March          87               1,63
April          60               2,4
May            44               2,6
June            0               1,8
July            0               1,4
August         51               1,7
September      22               1,6
October        64               1,9
November       70               2,2
December       55               2

It has to be possible for the user to filter year, month, as well as business unit and get information about sick leave days during the filtered time period (and in selected business unit) compared to the total sum of man-years in the same period (and unit). Calculated from the test data above, the desired result should be 488/22.36 = 21.82

However, I have not managed to do what I want. The main problem is, that calculation takes into account only those months with nonzero sick leave days and ignores man-years of those months with zero days of sick leaves (in example data: February, June, July). I have tried several alternative functions (all, allselected, filter…), but results remain poor. So all information about a better solution will be highly appreciated.

Does a simple DIVIDE(SUM(Table[Sick-days]), SUM(Table[Man-years])) work? If not, why not? — Alexis Olson
Thanks for your advice. I have tried that way of calculation already, but I think that reason for problems is that, if there is not sick days during month, in original data there is not any rows for those months. Information about man-years is in another table and if i connect data tables (man-years and sick days) on the basis of busines unit and time fields (connection type: one to many) calculation takes into account man-years only from those months during which sick days exist. — Phh

cgrafe cgrafe · Accepted Answer · 2020-03-18T20:09:35

It sounds like this has to do with the way DAX handles blanks (https://www.sqlbi.com/articles/blank-handling-in-dax/). Your context is probably filtering out the rows with blank values for "Sick-days". How to resolve this depends on how your data are structured, but you could try using variables to change your filter context or use "IF ( ISBLANK ( ... ) )" to make sure you're counting the blank rows.

PowerBI and filtered sum calculation

1 Answers