cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bryanc78 Regular Visitor
Regular Visitor

Multiple variables on x axis? Stacked column visualization

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

5 REPLIES 5
Community Support Team
Community Support Team

Re: Multiple variables on x axis? Stacked column visualization

Hi @bryanc78,

 

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

2.PNG3.PNG

 

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

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
bryanc78 Regular Visitor
Regular Visitor

Re: Multiple variables on x axis? Stacked column visualization

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.

bryanc78 Regular Visitor
Regular Visitor

Re: Multiple variables on x axis? Stacked column visualization

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?

Community Support Team
Community Support Team

Re: Multiple variables on x axis? Stacked column visualization

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
bryanc78 Regular Visitor
Regular Visitor

Re: Multiple variables on x axis? Stacked column visualization

@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?