How to add Rank to Pivot Table

are you looking for an average? what do you mean by rank exactly (i assume each line is someones ranking of importance from 1-6 on all categories...)

Take a look at the attachment - for example purposes i'm showing average by dept (assuming the SAP id is tied to the employees). If you click on the + sign for each row label you can see the details for each Sap ID in that department (so like what each employee answered) when you collapse it you see the average of the ranks given for that department...

---
edit
---
to make the example clear, i altered the SAP ID column, i may have misinterpreted what you use that for (i assumed an employee id or something) you can also have used Name in the pivot table where i used SAP id to get the same "dept average - details upon expansion" effect.

Sorry - posted the wrong book at first