Trend line from multiple choice question in Forms

[trndlnd]

Hi!

I have a Form with 12 multiple choice quiestions. Every answer have the options "Green", "Yellow", "Red" and "Other".

For some questions you can select up to two answers (Usually a colour and "Other" (Free text")

I want a trendline for each question in power BI for the colours. It must show the a line for "Green, a line for "Yellow" and a line for "Red" (y-axis)  with the dates on the x-axis.

Is there a Visual that does this automatically or can i download one?

..or do I have to split all the answers in Power automate so that I can sort each colour from each question into specific columns in Excel? If so, how do I do that? 🙂

[SamWiseOwl]

Hi @trndlnd 

I would bring it in to the Query Editor and then split the column by delimiter into rows:

You may want to split this question out into a seperate table for ease of manipulation.

[trndlnd]

Thanks for the reply!

I have already split the Columns by a delimiter, but into column, so that I can extract and display the comments from the answers. So now the colours are in one column and the comments (free text) is in the other, for all 12 questions.

I think I've found a way to do the trend line, but it just shows when there is data in the cells, and. If there is no data after last tuesday, the line just stops "in the air" that date. I want the line to go to zero when there is no data. 

An easy way to do this?

[SamWiseOwl]

Hi @trndlnd the trend line can be set based on a measure.

Measure to 0 = If( isblank([Your calculation]), 0, [Your calculation]))

[trndlnd]

Ah, that sounds like it will solve my issue.

Can you please explain/show where and how i set the trend line measure?

[SamWiseOwl · Accepted solution]

Right click on your column and choose New Measure.

Paste in the code about but replace the [Your Calculation] with your Count calculation that you dragged in to the visual. I imagine it will be something like Count(Table[Column]).

Feel free to share a screen shot of what you have including the Field well.

[trndlnd]

Thank you!

Now I got 12 fine charts with green, yellow and red lines 😎