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

Country	Average fee
US	234
China	44

<tbody>
</tbody>

 

[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]

Country	Number of sales	refunds	date	Orgin	Timestamp	Acccount number	Type
US	12345	1510
China	24555	1800
UK	85444	1788
France	84223	1086
Germany	1244	554
Belgium	5995	800

<tbody>
</tbody>

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.