I'm trying to do a fairly simple pivot table on a range of person financial transactions. Each transaction is a row in the source table, and has the following columns: Type, Description, Amount, Category.
I've got a working Pivot across these columns that can show me summed amounts for dimensions category and year, as well as dimensions month and year.
However, the number of categories I have is now too large to be useful, and so I'm looking to break these down into fewer broader categories and subcategories within those. For example, I would like to have a Level 1 category called 'Income' consisting of the Level 2 categories 'Salary', 'Contract', 'Interest'. I would then like to have the Pivot table give me a view across the higher-level categories and also allow me to drill down to see the subcategories.
What would be the best way of achieving this? Should I add another 'Category' column and then assign every transaction a Level 1 and Level 2 category? If I did that, how would I get the pivot table to initially pivot on the Level 1 category, but then allow me to dig into that to reveal the distribution of data across the Level 2 categories within?