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:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Column1[/TD]
[TD]Column2[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]No[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Column1[/TD]
[TD]Yes/Total[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]33,33%[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]50,00%[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]66,67%[/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Column1[/TD]
[TD]Column2[/TD]
[TD]Column3[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]X[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]X[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Y[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Y[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]X[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Y[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Y[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Y[/TD]
[TD]No[/TD]
[/TR]
</tbody>[/TABLE]

with result:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]X (Yes/Total)[/TD]
[TD]Y (Yes/Total)[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]50,00%[/TD]
[TD]0,00%[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]100,00%[/TD]
[TD]0,00%[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD][/TD]
[TD]66,67%[/TD]
[/TR]
</tbody>[/TABLE]


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