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:
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.)
All data and tables were created with power query and added to the data model through Course_ID columns in each query.