Converting Yes & No Data to visuals

[dw700d]

I have data that contains 45  different questions  and thousands of responses that include either "Yes", "No", "N/A". I would like to create a pie chart that shows the percentage of Yes Answers as a percentage of ALL responses including the N/A's? I would like to do this for each question.  Can someone help me with is? Below is an example of the data set .

Student	Is the Ball White	Is the Ball Blue	Ist the ball Brown	Is the Ball red	Is the ball pink	Is the Ball Yellow
1	N/A	Yes	N/A	No	Yes	No
2	Yes	Yes	Yes	No	No	No
54	NO	Yes	Yes	Yes	No	No
4	no	Yes	Yes	Yes	No	No
5	N/A	Yes	No	Yes	No	No
43	N/A	Yes	Yes	Yes	No	Yes
32	no	Yes	Yes	No	No	No
9	N/A	Yes	Yes	Yes	No	No
12	no	Yes	N/A	Yes	No	Yes
23	Yes	Yes	Yes	Yes	No	No
56	Yes	Yes	Yes	Yes	No	No
75	Yes	Yes	Yes	Yes	No	Yes
432	Yes	Yes	Yes	Yes	No	Yes

[v-lili6-msft · Accepted solution]

hi, @dw700d

You could try this way as below:

In Edit Queries, select Student column,then right click->Upivot other columns

or select all question columns ->Unpivot columns

Close&Apply

Then create a measure like this

Measure = CALCULATE(COUNTA(Table1[Value]))/CALCULATE(COUNTA(Table1[Value]),ALLSELECTED(Table1))

or

Measure 2 = CALCULATE(COUNTA(Table1[Value]))/CALCULATE(COUNTA(Table1[Value]),ALLEXCEPT(Table1,Table1[Student]))

Result:

and here is pbix please try it.

Best Regards,

Lin

[AlB]

Hi @dw700d

I think it would be considerably easier if you start by unpivoting the table