Pivot Table - dynamic selections but with % relative to total
There have been some very useful threads on % in pivot tables which have led me part of the way. Here's hoping someone can get me to the finish:
I'm using personal edition so I have set out the rough sheet outline as per the attached excel file.
I have a pivot table that lets me dynamically analyse the sum spent with suppliers according to one primary category and a second category against the country where it occurs, together with subtotals and totals for these amounts.
I need to show the percentage for each secondary dimension entry in each country against the total of that secondary dimension for ALL suppliers in that country for a particular year.
At the moment I have:
Sum
(USD)/Sum(Total<[Secondary Class of Business]> USD)
This works fine if I only select the year that I am reviewing in the list box at the top. However, if I want to analyse a particular supplier (e.g. by selecting a supplier from the supplier - sum (usd) list box) then this stops working, as the % column for each country becomes a % of the total of values for that individual supplier by country rather than a % of the total values for ALL suppliers by country for that year selected.
