Percentage in total row and using given dimension

[Tiina]

Imagine you have a list of employees of whom you want to report the absence rate (%) per month. You also want to report the absence rate (%) on the “upper level”, say area (where all those employees belong to) this time.

Absence hours are divided by minimum of Monthly working hrs and Hourly records, where monthly working hours is the “expected” working hours per each month. And hourly records are the records each employee has recorded each month.  Let's mark that:  nominator = min(Monthly working hrs, Hourly records)

So, the absence (%) = Absence (h) / Nominator

Now, this works fine on an employee-level. Also, the totals for nominator and absence (h) are completely fine - as you see on the upper screenshot.

 

Problem is now the total of absence (%), where the total should be 37,5h / 798,5h = 4,7%, not divided by 870h.

Also when I drop off the employee -dimension, the nominator shows the 870h - as you see on the lower screenshot. (I don’t exactly know why it is so, as it’s not taking the minimum of those two).

Somehow I should take the employee-level calculations into account in total and upper level, but how should I do it? I have tried several combinations of aggr, sum, total -functions but nothing seem to work as needed.