Your source data is already like a pivot table so needs some re-organisation in the background before presenting it to the user.
In the attached are two offerings (both use Power Query).
I've made the source data into a proper Excel table. I've also added a line to that source data to make sure that multiple positions for the same Position/Level/Company but different Pay are properly handled.
1. A Pivot table at cell G19. This is a bit of a cheat because I've hidden column J of the sheet because I'm not really using the pivot table to summarise data but to display it in a different way, so the Index column ensures that records remain separate. A bit messy.
2. A Power Query table at cell M19. This is more straightforward and more robust. There's just one tweak I made to that result table, I added conditional formatting in the first two columns to hide repeating items so that it looks a bit like what you wanted.
Both solutions need to be refreshed should the source data be changed. Use the Refresh All button on the Queries and Connections section of the Data tab of the ribbon.
|