Output Display Issues

[caadler]

My company recently did a survey, and I'm attempting to use PowerPivot to analyze the results. The responses for each question are on a 6-point scale from -3 to +3, without the 0. A sample format is below.

ID Q1 Q2 Q3
12 -2 3 2
13 1 -2 1
14 -3 1 1

I'm trying to get an output like this:

Q1 Q2 Q3
-3 43 21 17
-2 17 46 12
-1 12 0 55
1 17 24 32


Where the numbers in the grid are ID counts. I'm fairly competent with excel and pivot tables (normally, at least), but I can't seem to get the data to output how I want. I know I can strip out the data and do all of the counts manually, but I'd like to use PowerPivot for the slicers and additional data exploration.

The BI tool that I'm using to extract the data allows me to pull it and format in just about any way possible, so I have quite a bit of flexibility in the initial table.

Any help would be greatly appreciated. Thanks.

 

[scottsen]

I would think format like this would make it "easiest":

Question#, Value (-3 to 3)

Then you are just going to have Q# on columns, possible answer values on rows, and COUNTROW() in values.

 

[caadler]

Scott, thanks for your response. After playing with the data a bit, I have something that will work. Instead of flattening the data to 1 row per ID, I need 1 row per ID per question, with a column for each possible answer (-3, -2, etc) and a binary code in the column. Instead of counting ID's, I add the 1's up in each column for each question.