Creating a pivot table for different levels of data, from individual through entire organization

%3CLINGO-SUB%20id%3D%22lingo-sub-2213852%22%20slang%3D%22en-US%22%3ECreating%20a%20pivot%20table%20for%20different%20levels%20of%20data%2C%20from%20individual%20through%20entire%20organization%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2213852%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%26nbsp%3Bhave%20a%20folder%20of%20course%20surveys%20by%20students%20for%20a%20university.%20In%20one%20table%2C%20we%20would%20like%20to%20see%20the%20average%20result%20for%20each%20question%2C%20per%20instructor%2C%20Course%2C%20Department%2C%20School%20and%20University%20(grand%20total).%20The%20only%20way%20I%26nbsp%3Bhave%20been%20able%20to%20is%20create%20multiple%20tables%2C%20put%20them%20side%20to%20side%2C%20hide%20rows%20and%20put%20a%20filter%20in%20the%20column.%3C%2FP%3E%3CP%3EIt%20looks%20like%20this%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Overlapped%20Tables.JPG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F264313i470B0A5FCDFB38C2%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Overlapped%20Tables.JPG%22%20alt%3D%22Overlapped%20Tables.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EColumn%20C%20(Red%20arrow)%2C%20for%20the%20instructor%20level%2C%20is%20the%20table%20linked%20to%20the%20slicer%2C%20columns%20D%26amp%3BE%20are%20hidden%2C%20F%20(blue%20arrow)%2C%20for%20the%20department%20level%2C%20is%20the%20second%20table%20(with%20filter)%20and%20column%20G%20%2C%20university%20level%2C%20is%20a%20fixed%20column%20(all%20values%20chosen%2C%20copied%20%26amp%3B%20pasted).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%26nbsp%3Btried%20to%20get%20this%20all%20in%20one%20table%2C%20it%20looks%20like%20this%3A%26nbsp%3B%3C%2FP%3E%3CP%3E(I%20omitted%20the%20instructors%20names%20for%20security%20reasons%20and%20it%20made%20the%20table%20too%26nbsp%3B%20wide%20to%20show%20you%20the%20whole%20table.)%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22dlconnolly_1-1615896991632.jpeg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F264312i2BD2CFFD33DA9CE2%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22dlconnolly_1-1615896991632.jpeg%22%20alt%3D%22dlconnolly_1-1615896991632.jpeg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAll%20data%20and%20tables%20were%20created%20with%20power%20query%20and%20added%20to%20the%20data%20model%20through%20Course_ID%20columns%20in%20each%20query.%3C%2FP%3E%3CP%3EThank%20you%2C%3C%2FP%3E%3CP%3EValerie%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2213852%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

 

I have a folder of course surveys by students for a university. In one table, we would like to see the average result for each question, per instructor, Course, Department, School and University (grand total). The only way I have been able to is create multiple tables, put them side to side, hide rows and put a filter in the column.

It looks like this:

Overlapped Tables.JPG

 

Column C (Red arrow), for the instructor level, is the table linked to the slicer, columns D&E are hidden, F (blue arrow), for the department level, is the second table (with filter) and column G , university level, is a fixed column (all values chosen, copied & pasted).

 

When I tried to get this all in one table, it looks like this: 

(I omitted the instructors names for security reasons and it made the table too  wide to show you the whole table.)

dlconnolly_1-1615896991632.jpeg

 

All data and tables were created with power query and added to the data model through Course_ID columns in each query.

Thank you,

Valerie

0 Replies