Visualizing textual data, like YES/NO questions

[frankfurter]

Howdy folks - first post! Couldn't find the answer to this in the forums, and not sure I can even ask it right, but here goes.

I have a spreadsheet similar to this:

            2014   2015

Site 1    YES     YES

Site 2    NO     YES

Site 3    YES     NO

I'd like a visualization / horiz bar chart that would simply show each site, with the two years as sub-groupings under each site, then a solid 100% bar that was either red for NO or green for YES. Or any variation, I'm open, but I'm getting stuck on the YES/NO fields simply getting counted, which doesn't help me (unless I convert the YES's to 0 and NO's to 1 or something). 

Any thoughts on how to approach this? Let me know if I can clarify or explain in more detail. 

[USG_Phil · Accepted solution]

I've dealt with this myself (though in my case it was TRUE/FALSE values).  Here's how I solved it:

 - Created a new calculated field called "Achieved or Missed" (you should use something appropriate for your data), assign it a numerical value of '1' whenever the original field's value is 'Yes' or a numerical value of '0' whenever the original field's value is 'No'

- Create 1 new measure called '% Achieved' --- SUM('Achieved or Missed') / COUNT('Achieved or Missed')

- Create 1 new measure called '% Missed' --- (COUNT('Achieved or Missed') - SUM('Achieved or Missed')) / COUNT('Achieved or Missed')

- Use a Stacked Bar (or Column) chart and plot the 2 measures.

[frankfurter]

Thanks Phil. Clever approach, I've gotten closer based on your input, so thanks. The multiple years are adding to the complexity for this novice. I'll keep at it!

[USG_Phil]

Frank,

Glad it helped. 🙂

As to the 'Years' ... if I understood your data correctly, you should be able to put that column in the 'Axis' of the Stacked Bar chart and have it display the Yes/No % bar for each individual year.