Creating Line Charts

[e175429]

Hello all,

I have survey data with 3 questions. In PowerBi, I need to recreate 3 line charts based on each question, like the ones below:

This chart is based on question "How satisfied were you with your response?"

The data dark blue points show the avarage answers of "Extremely satisfied" and the orange data points show the average answers of "not at all satisfied" by month/year.

 

This chart is based on the question "How satisfied were you with the CX team?"

The data dark blue points show the avarage answers of "Extremely satisfied" and the orange data points show the average answers of "not at all satisfied" by month/year.

 

This chartis based on the question "Do you feel our team answered your inquiry promptly?"

The data points show the average "Yes" answer by month.

 

Here is a copy of my data:

  Data 

I don't know how to upload PowerBi files otherwise I would have uploaded that. And when I tried to upload the excel file, it says "The file type (.xlsx) is not supported.". Therefor I had to upload it in Google sheets. 

Any help on the steps I would need to take to create these line charts in PowerBi will be most appreciated. 

Thank you! 

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

In the attached file, I tried to select certain answers per each question, and then turn on Trend line feature in the format pane for each visualization.

Q1 %: = 
VAR _allresponse =
    CALCULATE (
        [Count response:],
        REMOVEFILTERS ( Question_fact[how satisfied were you with your response?] )
    )
VAR _target = [Count response:]

RETURN
    DIVIDE ( _target, _allresponse )

Q2 %: = 
VAR _allresponse =
    CALCULATE (
        [Count response:],
        REMOVEFILTERS ( Question_fact[how satisfied were you with our CX Team?] )
    )
VAR _target = [Count response:]

RETURN
    DIVIDE ( _target, _allresponse )

Q3 %: = 
VAR _allresponse =
    CALCULATE (
        [Count response:],
        REMOVEFILTERS ( Question_fact[Do you feel that our team answered your inquiry promptly?] )
    )
VAR _target = [Count response:]

RETURN
    DIVIDE ( _target, _allresponse )

[e175429]

Hello, 

I'm back with a follow-up question.

If you look at the file you uploaded, and hover over any of the data points, you'll see that the Months don't match up wth the dates on the axis.

I have this fixable by putting "Month-Year" on the x-axis instead of "Month-Year sort".

However, when I put Month-Year, the Trend option disappears from the format pane so I can't put the trend line back.

Is there a workaround to this? To have the dates match up on the x-axis and include trend lines?

Thank you!

[e175429]

Hello,

Please ignore my previous question. To get rid of the drop down i had to turn off "Auto date/time" under Time Intelligence in the settings.

Thank you very much.

Regards

[e175429]

Hello,

So, I'm not sure why but when I created the Calendar table, my "Month-Year-sort" has a dropdown and yours does not, and I copied your code exactly.

Your code and table:

My code and table: