10 Replies Latest reply: Aug 23, 2012 6:14 AM by dasbmx32 RSS

    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.

       

      Any ideas would be gratefully appreciated!