I agree - the fact that this is not possible is a little shocking and has caused us to create multiple tabs or tables with different layers/levels for our end users. It's not an ideal design or experience. A collapsable or expandable pivot table is certainly needed.