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:

 

[sandy666]

try to add Calculated Field

 

[snjpverma]

Ok. I'll try it. Thanks

 

[sandy666]

btw. why you have value 2 for total sold? should be there 0?

 

[snjpverma]

btw. why you have value 2 for total sold? should be there 0?

Each of the Sales Executive has sold some number of items so it cannot be zero. "Total Defectives products" can be zero though.
Secondly, I am unable to get the Calculated Field to work. Could you please assist? it gives #VALUE! error.

 

[sandy666]

Each of the Sales Executive has sold some number of items so it cannot be zero. "Total Defectives products" can be zero though.

I don't understand your logic
Yellow: both are Defective but not Sold, so why Sold is 2 in your expected table?

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

 

[snjpverma]

Thanks for your consistent response. I highly appreciate that.

To answer your question,
This is a list of sold items, so all the items are sold. There are only 3 columns; first is the names of Sales Executives, second column is the name of the Product and the third column is the informs us if the Product that was sold was defective or not.
Therefore, there is no column which defines whether a product is sold or not. XYZ is a just a pseudo name for the product that I gave it for sampling purpose.
Out of all the sold items, the Column C shows which ones are defective and which ones are not (demonstrated by YES/NO).
So, the Pivot report is basically to notify the Sales people about the percentages of the defective products that they have been selling. So that they can check the product thoroughly before selling.
Hope this clarifies the confusion.

 

[sandy666]

something like this?
some cosmetics required

 

[snjpverma]

This is exactly what I need. But how do I do it through a Pivot?

 

[sandy666]

with my solution you will need Power Query, Pivot Table and Calculated Field

see example

it can be done without Pivot Table but you want Pivot so there is a Pivot

 

[snjpverma]

I am a newbie in Power Query. Could you send a video reference that I need to watch for Power Query guidance for this particular scenario? I opened this table in Power Query but I do not know what to do further in the Query editor.
Much appreciated!