Sub value vs total in Pivot

[Bilingual]

Hi, i have an Excel table, which contains Countries in one column and a number of variables in other columns, sales per month, average fee etc.

The very simple question is how i can create a Pivot table where one Country is matched up against the Total values for all countries!


Kind regards Daniel

Example

[TABLE="width: 500"]
<tbody>[TR]
[TD]Country[/TD]
[TD]Average fee[/TD]
[/TR]
[TR]
[TD]US[/TD]
[TD]234[/TD]
[/TR]
[TR]
[TD]China[/TD]
[TD]44[/TD]
[/TR]
</tbody>[/TABLE]

 

[AlexandraT]

Would you like to compare how a country is doing compared to all other countries in one of the categories/variables? Like a top 10 and bottom 10 sort of thing?

 

[Bilingual]

Would you like to compare how a country is doing compared to all other countries in one of the categories/variables? Like a top 10 and bottom 10 sort of thing?

Hi Alexandra, i would like to compare for example Australia, to the Total countries including Australia on the variables,

I can make a formula which acts as filter, which separates Australia and the Rest of the countries , - but then Australia is not a part of the Total Countries.

 

[AlexandraT]

Could you provide a sample of what you have and also what you want to achieve? How it would look like and how you want to compare data: just show Australia numbers and total numbers? or use some formatting? or a formula to see the difference between total and australia numbers? etc.

 

[Bilingual]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Country[/TD]
[TD]Number of sales[/TD]
[TD] refunds[/TD]
[TD]date[/TD]
[TD]Orgin[/TD]
[TD]Timestamp[/TD]
[TD]Acccount number[/TD]
[TD]Type[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]US[/TD]
[TD]12345[/TD]
[TD]1510[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]China[/TD]
[TD]24555[/TD]
[TD]1800[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]UK[/TD]
[TD]85444[/TD]
[TD]1788[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]France[/TD]
[TD]84223[/TD]
[TD]1086[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Germany[/TD]
[TD]1244[/TD]
[TD]554[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Belgium[/TD]
[TD]5995[/TD]
[TD]800[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Hi i have tried to show the structure without filling all data out, from this data i would like to extract the percentage of refunds from the US compared to average total and show it in a pivot graph.

 

[AlexandraT]

Add "refunds" as a field in the Values section (you can add a field multiple times if need be). Then go to the field settings, select "Average" (this will show average values), then go to "Show values as" and then select "% of Grand Total" There are some other options there if you want % of column total or row total etc. This should display % of refunds for a country compared to the Grand Total which would be 100%. You can then use the pivot table to create a pivot graph which would display the percentages as well. Is that what you had in mind?

Alex

 

[Bilingual]

Thanks, but i need to show a graph, where for example the period Januar, February and March is the axis and where one curve shows the US percentage of refunds and the other curve shows the average total.

 

[Bilingual]

 

[AlexandraT]

You can have 2 "refunds" fields in a pivot. One would show averages only, the other would show %. Just create the pivot chart and it will show both.

 

[Bilingual]

You can have 2 "refunds" fields in a pivot. One would show averages only, the other would show %. Just create the pivot chart and it will show both.

Thanks, but somehow i can not make it appear in a graph.