Forum Discussion

e175429's avatar
e175429
Icon for Helper IV rankHelper IV
1 year ago
Solved

Creating Line Charts

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 y...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    1 year ago

    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 )
Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
1 year ago

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's avatar
    e175429
    Icon for Helper IV rankHelper IV
    1 year ago

    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:

     

  • e175429's avatar
    e175429
    Icon for Helper IV rankHelper IV
    1 year ago

    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's avatar
    e175429
    Icon for Helper IV rankHelper IV
    1 year ago

    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!