Hi Laura, I'm unable to open the workbook as it is not a packaged workbook. You might want to pivot the data first in order to count number of X's in the table. If this doesn't work try attaching a TWBX file.

Hi Laura,

.Twb files don't contain the data with them.  If you resave your file as a .twbx instead then the data will carry over.

Anyway, on your data source screen, select all 11 columns, right click, and select "pivot".  This will put all of your column headers into one field (call it whatever you'd like - maybe "Headers") and their values in another field (call it something like "Values").

Once you have the data pivoted, you can create a calculated field to count the number of 9s for each Header value - calling this field "9 Count" for this example:

sum(if [Value] = 9 then 1 end)

Drag your [Header] field onto columns.  Drop your [9 Count] field onto rows (make sure it's continuous).  Set your mark to bars.  Right click on [Header] and sort descending based on the [9 Count] field.

Let me know if you have any questions.

Best,

Paul

I could get the results using pivot and bins

You will need to get n1 to n10 in same column using some pivot method in sql server in custom sql or view

Hi Laura,

Not sure what you mean by something being amiss.  The count is working correctly to identify the number of 9s for each header.  The last piece you wanted was the sort - so if you right click on [Headers] and select sort - you can set it by descending by [9Count].

Let me know if there's another piece to this that I'm missing.

Paul

If you add up each individual bar they add up to 13 occurences of 9s, which matches your analysis.  Not sure what I'm missing here.

You're very welcome Laura!  Happy to help!