# Percent of Total - How to build report

#### zico8

##### Board Regular
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

##### MrExcel MVP, Moderator
Would this be any use?
Formula in E2 is copied down.

Excel Workbook
ABCDE
1Column1Column2
2ANoA
3AYesB50.00%
4ANoC66.67%
5BNo
6BYes
7CYes
8CYes
9CNo
10
% Yes

#### zico8

##### Board Regular
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

##### Board Regular
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

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

#### archangel99

##### New Member
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

##### Board Regular
archangel99 - what you added to each field?
Values: ??

#### archangel99

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

#### zico8

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

Last edited:

#### Peter_SSs

##### MrExcel MVP, Moderator
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
ABCDEFG
1DataX/YY/NXY
2AXNo0.00%
3AXYesB100.00%0.00%
4AYNoC66.67%
5BYNo
6BXYes
7CYYes
8CYYes
9CYNo
10
11
% of Total

1,082,360
Messages
5,364,939
Members
400,815
Latest member
Joaquin Phoenix

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...