How to set the Pivot Table Layout

[snjpverma]

As shown in below Table. "F" has sold 2 items out of which one is defective. i.e. 50% of what he has sold is defective. How do i represent this in Pivot Table?
I am unable to figure out the Layout.

Raw Data:

Sales Executive	Products Sold	Defective
F	XYZ	NO
D	XYZ	NO
F	XYZ	YES
C	XYZ	YES
A	XYZ	YES
C	XYZ	NO
B	XYZ	NO
B	XYZ	NO
D	XYZ	NO
A	XYZ	YES
G	XYZ	YES
G	XYZ	NO
G	XYZ	NO
E	XYZ	NO

I tried as shown below, but I get it as 100% for all the Sales executives. Please suggest as I am definitely missing something.

My Pivot:

 

[snjpverma]

Please let me know if I need to change my Data layout in case the required output is not possible with my current layout. Also, if the question is not clear enough, please let me know.
I want to see the Percentage of Defective items. Please assist.

 

[sandy666]

something like this ?

 

[snjpverma]

something like this ?
View attachment 1318

No Sandy666. thanks for the try but that is not the desired output.
Ideally it should be 50% for "F" because he has sold two products out of which 1 is defective. hence (1 / 2 *100 = 50%)
Similarly, "A" has sold 2 products and both are defective, so the Defective Item % would be 100% for him and not 14.29% as reflecting in your sample.

 

[sandy666]

you didn't explain it properly in the first post and didn't show expected result

maybe this

 

[snjpverma]

This is also a good way to show the sales executives the percentage of Defective items sold. Could you guide me how you did that?. Thanks in advance. I'll check your reply tomorrow morning.
Good night.

 

[sandy666]

I don't know why the picture in the post is resized to smaller

here is much better picture: https://i.postimg.cc/ZK1nB7Gj/pt3.jpg

 

[snjpverma]

Thanks for your reply. What is that value in column section?

 

[sandy666]

add fields to appropriate area and value are you asking will add itself

 

[snjpverma]

I have installed the tool to capture image now. So, I can show my desired output as well as the raw data.

RAW DATA:

Book1
	A	B	C
1	Sales Executive	Products Sold	Defective
2	F	XYZ	NO
3	D	XYZ	NO
4	F	XYZ	YES
5	C	XYZ	YES
6	A	XYZ	YES
7	C	XYZ	NO
8	B	XYZ	NO
9	B	XYZ	NO
10	D	XYZ	NO
11	A	XYZ	YES
12	G	XYZ	YES
13	G	XYZ	NO
14	G	XYZ	NO
15	E	XYZ	NO
Sheet1

Below is the desired Output needed in Pivot:

Book1
	G	H	I	J
17	Sales Executive	Total Defectives	Total Sold	Defective %
18	A	2	2	100%
19	B	0	2	0%
20	C	1	2	50%
21	D	0	2	0%
22	E	0	1	0%
23	F	1	2	50%
24	G	1	3	33%
25	Grand Total	5	14
Sheet1

Cell Formulas
Range	Formula
J18:J24	J18	=H18/I18