Percent of Total - How to build report

[zico8]

Hi,

I have some task - that looks like very easy, but... I cannot find solution

I have data like this:

Column1	Column2
A	No
A	Yes
A	No
B	No
B	Yes
C	Yes
C	Yes
C	No

<tbody>
</tbody>

And want to get report showing the percent of Yes/Total:

Column1	Yes/Total
A	33,33%
B	50,00%
C	66,67%

<tbody>
</tbody>

How to build that?
Does Pivot Table do this? Or I need some transformation in PowerQuery?

 

[Peter_SSs]

Would this be any use?
Formula in E2 is copied down.

Excel Workbook
	A	B	C	D	E
1	Column1	Column2
2	A	No		A	33.33%
3	A	Yes		B	50.00%
4	A	No		C	66.67%
5	B	No
6	B	Yes
7	C	Yes
8	C	Yes
9	C	No
10
% Yes

 

[zico8]

The result is OK but I am looking for some solution that I can create that report automatically in the gentle way - no matter how many data I have in my table.
More advanced sample thet need to be solved too is to have two parameters for each record of my table, like:

Column1	Column2	Column3
A	X	No
A	X	Yes
A	Y	No
B	Y	No
B	X	Yes
C	Y	Yes
C	Y	Yes
C	Y	No

<tbody>
</tbody>

with result:

	X (Yes/Total)	Y (Yes/Total)
A	50,00%	0,00%
B	100,00%	0,00%
C		66,67%

<tbody>
</tbody>

so... I think that should be some solution to do this with PT

 

[zico8]

Hi,

I have found that if I change each "Yes" to 1, and each "No" to 0 - creating these reports will be very easy in Pivot Table.
But is there any way to do it?

 

[zico8]

Done this by adding one column with formula:
If(Column3="Yes";1;0)

 

[archangel99]

Hi,

I have found that if I change each "Yes" to 1, and each "No" to 0 - creating these reports will be very easy in Pivot Table.
But is there any way to do it?

You can pull the yes/no column into your pivot table (column) and then compute the column percentage.

 

[zico8]

archangel99 - what you added to each field?
Rows: 'Header1';
Columns: 'Header2', 'Y/N';
Values: ??

 

[archangel99]

Count of y/n. Count is field independent so can be count of any of the field.

 

[zico8]

But the result is different and incorrect.
Please see B+X. We have only YES, so the result for YES should be 100%

 

[Peter_SSs]

Again this is without a PT, but see if any use.

Data in columns A:C is made into a formal table ('Table1' for me). This allows automatic expansion/contraction of the results.
Formula in E2 is copied down as far as you might ever need.
Formula in F2 is copied across and down as far as the column E formulas.

Excel Workbook
	A	B	C	D	E	F	G
1	Data	X/Y	Y/N			X	Y
2	A	X	No		A	,$E2,Table1:],F$1,Table1:],"Yes")/COUNTIFS(Table1:],$E2,Table1:],F$1),"")]50.00%	0.00%
3	A	X	Yes		B	100.00%	0.00%
4	A	Y	No		C		66.67%
5	B	Y	No
6	B	X	Yes
7	C	Y	Yes
8	C	Y	Yes
9	C	Y	No
10
11
% of Total