Need help with a percentage calculation in a pivot table

jeff_cp

Board Regular
Joined
May 21, 2015
Messages
73
I'm working in Excel 2019 on a pivot table that has two rows of data. The first is the Employee and the second is the Warranties they've sold. The values show information like the # of warranties sold, the total amount sold, etc.

I'm looking for a way to calculate a % for each Warranty for for each Employee. For example, I have an employee named Kristen that has 747 total warranties since the first of the year. The warranties she has sold include the following:

$0-499 12MOS PARTS NO LABOR (total qty is 1)
$500-$999 12MOS PARTS NO LABOR (total qty is 1
CUSTOMER DECLINED EXT WARRANTY (total qty is 744)
$1000 & UP 6MOS PARTS NO LABOR (total qty is 1)

So our of the 747 total warranties, 1 was the $0-499 12MOS PARTS NO LABOR so the % calculation would be 1/747 for a % of 0.13%.

I can't figure out how to divide the total warranties by the number of each individual warranty since the total resides with the Employee while the warranty totals reside with each warranty.

Any help with this would be greatly appreciated!
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
798
Office Version
  1. 365
Platform
  1. Windows
Assuming the below is what you want as output then.
Pull the quantity field into your pivot twice.
The first time just as Sum
The 2nd time
  • Show values as
  • % of Parent Total
  • Base Field = Employee

1618924207988.png




Book1
EFGH
4EmployeeWarrantiesSum of QtySum of Qty2
5Kirsten$0-499 12MOS PARTS NO LABOR (total qty is 1)10.13%
6$1000 & UP 6MOS PARTS NO LABOR (total qty is 1)10.13%
7$500-$999 12MOS PARTS NO LABOR (total qty is 110.13%
8CUSTOMER DECLINED EXT WARRANTY (total qty is 744)74499.60%
9Kirsten Total747100.00%
10JohnProduct11011.76%
11Product22023.53%
12Product355.88%
13Product45058.82%
14John Total85100.00%
15Grand Total832
Sheet1
 
Solution

jeff_cp

Board Regular
Joined
May 21, 2015
Messages
73
Wow that was surprisingly easy lol. Thank you much for the help! I love this site!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,132,704
Messages
5,654,829
Members
418,155
Latest member
demasisi

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