Is it possible make the X axis label dynamic?

Question & Answer
, , , ,
1

I’ve got original excel data which shows some questions and their options and how many people chose the option. The row data is like:
Question Resp_1 Resp_1_count Resp_2 Resp_2_count Resp_3 Resp_3_count
qwert AA 15 BB 3 CC 2
asdfg DE 16 FG 2 HI 2
zxcvb AA 15 BB 3 CC 1

I built the visual of questions and I’m going to build a bar chart, once I clicked the specific question, the chart will display all options regarding the question as X axis labels, and count value as Y. Not sure if possible to build the chart, though

1 Like
2

Hello @Sigrid, in order to set this up to utilize the Response options for your x-axis, you would want to set up your table differently. Your X-axis will want to reference a single column. Instead of having 4 response column, you would want a single response column that contains 4 different values for each question. It would looke like this:

Question Response
qwert AA
qwert BB
qwert CC
qwert DD

If you reorder your table in Excel, you can implement this by adding the 2 fields to your field wells in the visual. Let me know if this helps!

3

Hi @DylanM , thank you for your reply. ::Your suggestion can truly solve the problem. However, I’m seeking another solution which does not have to change the row data. There are also some other information in row data, like the score of every option and the mean score of every question. And the row data updates per month. So I’d rather not change table in Excel. Is there any other way like creating a calculated field?

1 Like
4

Hello @Sigrid, this functionality requires a transformation of your data. QuickSight is not built to transform your data for visuals but rather to handle that on the dataset level. You can query data differently in SQL, change the format of data in an excel sheet and import it, or create a new view in your database to match the format expected. A calculated field doesn’t have the ability to alter data in the same way.

Altering your data before bringing it into the analysis is going to be the only way to accomplish this the way you want. I will mark this response as the solution, but let me know if you have any remaining questions.