Excel pivot table question – How to get subtotals for a row area field?
I have the following situation in database:
Department table holds all departments in the company.
Employee table holds all employees, together with their department id and AllowedAbsenceDays field, which tells us how many days is the employee alowed to be absent in the current year.
Absence table holds all absences that occur in the company. It has employee id and date of absence, and also the reason of the absence (vacation, sick leave, personal days, and potentially more in the future...)
I am supposed to create a report which lists all employees and shows how many days they are allowed to be absent together with how many days have they been absent (and what is the reason of absence).
I have grouped the data by employee and by reason of absence:
This is what I am able to get so far, by putting the Department, Employee and Allowed Absence Days in the pivot row area, Reason in the column area and Sum of Days absent in the data area:
The problem is that I am not able to get the subtotals of allowed absence days per department (which is a firm requirement). The final report should look somewhat like this (i have photoshopped this).
Is there any way to get these subtotals? Maybe I should prepare the data for the pivot table in a different way? Please note that the Grand Total column should include only the actual days of absence (not the Allowed Absence Days).
Example workbook is available for download here
Thanks to everybody who is still reading :)
P.S. The real case is different (in the problem domain). This is a somewhat contrived example, but the basic problem is the same.