Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dw700d
Post Patron
Post Patron

Converting 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 .

 

StudentIs the Ball WhiteIs the Ball BlueIst the ball BrownIs the Ball redIs the ball pinkIs the Ball Yellow
1N/AYes

N/A

NoYesNo
2YesYesYesNoNoNo
54NOYesYesYesNoNo
4noYesYesYesNoNo
5N/AYesNoYesNoNo
43N/AYesYesYesNoYes
32noYesYesNoNoNo
9N/AYesYesYesNoNo
12noYesN/AYesNoYes
23YesYesYesYesNoNo
56YesYesYesYesNoNo
75YesYesYesYesNoYes
432YesYesYesYesNoYes
1 ACCEPTED SOLUTION
v-lili6-msft
Community Support
Community 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

7.JPG

8.JPG

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:

9.JPG10.JPG

 

and here is pbix please try it.

 

Best Regards,

Lin

 

 

 

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-lili6-msft
Community Support
Community 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

7.JPG

8.JPG

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:

9.JPG10.JPG

 

and here is pbix please try it.

 

Best Regards,

Lin

 

 

 

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
AlB
Super User
Super User

Hi @dw700d

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.