Forum Discussion
Post PatronConverting Yes & No Data to visuals
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 |
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
2 Replies
v-lili6-msftCommunity Support
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
