Sub value vs total in Pivot

Bilingual

Board Regular
Joined
Oct 1, 2010
Messages
183
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

CountryAverage fee
US234
China44

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

AlexandraT

Board Regular
Joined
Mar 23, 2015
Messages
144
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

Board Regular
Joined
Oct 1, 2010
Messages
183
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

Board Regular
Joined
Mar 23, 2015
Messages
144
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

Board Regular
Joined
Oct 1, 2010
Messages
183
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

CountryNumber of sales refundsdateOrginTimestampAcccount numberType
US123451510
China245551800
UK854441788
France842231086
Germany1244554
Belgium5995800

<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

Board Regular
Joined
Mar 23, 2015
Messages
144
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

Board Regular
Joined
Oct 1, 2010
Messages
183
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Oct 1, 2010
Messages
183
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
 

AlexandraT

Board Regular
Joined
Mar 23, 2015
Messages
144
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

Board Regular
Joined
Oct 1, 2010
Messages
183
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,304
Messages
5,600,865
Members
414,409
Latest member
FloordAlex

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top