Need help with a percentage calculation in a pivot table

[jeff_cp]

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!

 

[Alex Blakenburg]

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

Book1
	E	F	G	H
4	Employee	Warranties	Sum of Qty	Sum of Qty2
5	Kirsten	$0-499 12MOS PARTS NO LABOR (total qty is 1)	1	0.13%
6		$1000 & UP 6MOS PARTS NO LABOR (total qty is 1)	1	0.13%
7		$500-$999 12MOS PARTS NO LABOR (total qty is 1	1	0.13%
8		CUSTOMER DECLINED EXT WARRANTY (total qty is 744)	744	99.60%
9	Kirsten Total		747	100.00%
10	John	Product1	10	11.76%
11		Product2	20	23.53%
12		Product3	5	5.88%
13		Product4	50	58.82%
14	John Total		85	100.00%
15	Grand Total		832
Sheet1

 

[jeff_cp]

Wow that was surprisingly easy lol. Thank you much for the help! I love this site!!!

 

[Alex Blakenburg]

Glad we could help, thanks for letting me know.