Multiple variables on x axis? Stacked column visualization

[bryanc78]

I have data that is from a SharePoint list that is similar to a test you would take.  Columns are Name, Test Date and then another 30 columns that are the test questions.  Each test question column can have say 5 answers.

I'm hoping to to create a stacked column chart that shows the answers to each question.  For example, the first bar would show Question 1 with the total count of the 5 answers.  Then next to that bar would be question 2 and so on.  That way, one visualization would show all the questions and quickly show me which question is the worst performing one. 

Being able to show these as a % would also be good as well as a cluster chart to show different dates.

I'm thinking the way my data is set up is causing the issue.  Below is how the data is laid out

 Name        Test Date            Question 1           Question 2            Question 3             Question 4
    Bill           12/1/2018                Red                      Truck                       Tire                        Tuesday

[mike9191]

Hello, I have a similar doubt. I would like to create a graphic with 4 variables::

1. Number of people (#)

2. Time, i.e., "1st quarter", "2nd quarter", "3rd quarter" and "4th quarter"

2.1 Within each quarter there would be 3 buckets, i.e., "Bucket 1", "Bucket 2" and "Bucket 3".

2.1.1 Every bucket within all quarters would be a column divided into 3 categories (like stacked bars): "Orange", "Yellow" and "Green".

Below you can find a draft of the graph 🙂

I appreaciate any hint. 

[v-yulgu-msft]

Hi @bryanc78,

In Query Editor mode, please unpivot table to convert its structure to below format.

Place [Questions] onto X-axis and place [Answer] into legend of a stacked column chart.

Best regards,

Yuliana Gu

[bryanc78]

So, I got it to work but now that it created multiple rows, it shows each person took the test multiple times instead of just once.  Any way to get the non unpivot columns to only show a value once?

For example, if bob took 1 test with 30 questions, I need Bob to show only 1 test and not 30 tests.  Hope this makes sense

Can I make a copy of the table, strip out everything but name, test date and the new unpivot column and value column then use that new table to get visualizations on the test questions?  That way my other visualizations are not impacted?

[v-yulgu-msft]

Hi @bryanc78,

---

 For example, if bob took 1 test with 30 questions, I need Bob to show only 1 test and not 30 tests.  Hope this makes sense

---

Not very clear about your requirement. Does DISTINCTCOUNT meet your requirement? Please illustrate your scenario with some sample data.

---

Can I make a copy of the table, strip out everything but name, test date and the new unpivot column and value column then use that new table to get visualizations on the test questions?  That way my other visualizations are not impacted?

---

Of course you can.

Regards,

Yuliana Gu

[bryanc78]

@v-yulgu-msft

So this has been working well for me but now my refresh times are taking an hour to complete and I'm thinking it's because of the duplicate tables being updated at the same time. Could that be a reason?

[bryanc78]

That looks good

I'll have to research this more as when I just unpivoted one column, my entire table is now showing Error for the values

DataSource.Error: We couldn't parse OData response result.  Error: A value without a type name was found and no expected type is available. When the model is specified, each value in the payload must have a type which can be either specified in the payload, explicitly by the caller or implicitly inferred from the parent value.