Forum Discussion
summarize data by cell values
Hi--
Trying to solve a summarization problem. I'm working with survey data for a client. Currently the data output is structed with questions as the column headers, and the cell values (converted from text to numerical) are the responses, something like this:
| Q1 | Q2 | Q3 | Q4 | Q5 |
| 1 | 1 | 1 | 0 | 0 |
| 0 | 1 | 1 | 1 | 1 |
| 1 | 1 | 0 | 0 | 0 |
| 0 | 1 | 0 | 1 | 1 |
| 1 | 0 | 0 | 0 | 1 |
| 0 | 1 | 1 | 1 | 0 |
However, my clients are very interested in stacked charts to review survey responses, so I need the results to be transformed into something like this:
| 1 | 0 | |
| Q1 | 3 | 2 |
| Q2 | 4 | 1 |
| Q3 | 3 | 0 |
| Q4 | 3 | 0 |
| Q5 | 3 | 0 |
This does is not seem to be a transpose or unpivot problem (I've tried), but I suspect it's a sophisticated summarization problem. My DAX skills are decent but not amazing. I have created lots of tables using the SUMMARIZE function, but I just can't seem to wrap my head around this problem. Any help would be very appreciated.
Thank you in advance!
