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

# 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

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!

• ###### Re: Pivot Table - dynamic selections but with % relative to total

Hi,

You are use the following as your expression:

Sum(USD)/Sum({\$<Year ={2011}>}Total<[Second Dimension]> USD)

This should in my oppinion give you 100% as the result, if that is the case change Total<[Second Dimension]> to Total<[Primary Dimension]>

I also noticed that you are using a hard coded year. This would mean that irrespective of what you chose as year your denominator would always be a function of Year 2011. Is that what you want? If not your new expression should be:

Sum(USD)/Sum(Total<[PrimaryDimension]> USD)

Regards

Rahul

• ###### Re: Pivot Table - dynamic selections but with % relative to total

Thanks Rahul,

I want to keep each entry as a percentage of the secondary dimension total of that country, i.e. some variation on:

Sum(USD)/Sum(Total<[SecondaryDimension]> USD)

This sort of works when only the year is selected (e.g. '2011'), except that the final Totals column percentage does not always have '100%' listed in the % column.

Also, this still gives the problem that, if I selected a specific supplier from the list box, the percentage figure for that supplier is a percentage of the secondary dimension for that supplier.

What I need to see is:

-% figure of what that supplier repesents for the total of 2011 secondary dimension in that country.

e.g. Supplier X represents 2% of total category 1a spend in Country A in [Year].

• ###### Re: Pivot Table - dynamic selections but with % relative to total

Any more ideas on how to approach this would be gratefully appreciated!

• ###### Re: Pivot Table - dynamic selections but with % relative to total

Hi,

I think you may need a combination of Aggr() and TOTAL, or maybe Set Analysis to remove a selection. I'm not sure as I don't fully understand your requirement! Can you provide an example of a table with some dummy values in it, explaining what the values should change to when you make a particular selection(s). That would help.

Jason.

• ###### Re: Pivot Table - dynamic selections but with % relative to total

Thanks Jason.

I've updated a mock-up excel table with dummy values. The key selections will always be which year we are looking at, and then we will drill down into individual suppliers. When we do that, we want to see what % that supplier has by Secondary Dimension and by Country of the total for that Secondary Dimension by Country (for the selected year). - Hopefully the second tab shows you what I mean.

Regards,

• ###### Re: Pivot Table - dynamic selections but with % relative to total

Ah!  I think all you need to do is add a set modifier to your expression.  Try:

Sum(USD)/Sum({<Supplier=>} TOTAL <[Second Dimension]> USD)

Hope this helps,

Jason

EDIT: I don't think you want to use TOTAL, actually.  Try:

Sum(USD)/Sum({<Supplier=>} USD)

Jason

• ###### Re: Pivot Table - dynamic selections but with % relative to total

With the edit, I think that's it.

Thank you very much indeed!

• ###### Re: Pivot Table - dynamic selections but with % relative to total

Hello, I'm trying to do a similar thing, but can't get the expression to work right. Please see file attached. By month, I need to show % of total for that month. When excluding a brand from the selection, the % for the remaining brands needs to stay the same, so always the % of total for ALL brands for that month. What expression is needed for this purpose?

• ###### Re: Pivot Table - dynamic selections but with % relative to total

Try Like:

sum (Sales)/sum({<PrimaryD=>} Total <Month>Sales)

• ###### Re: Pivot Table - dynamic selections but with % relative to total

Thanks a lot, this works fine now!