Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Line Chart

Hi,   is it possible to get a line chart in power-bi which looks like this? I just want to display 4 values, minimum, maximum, average and the actual value. I tried and got this output. ...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

    I created a sample pbix file(see attachment), please check whether that is what you want.

    1. Create a calculated column as below

    Type = 
VAR _max =
    MAX ( 'Table'[Spend] )
VAR _min =
    MIN ( 'Table'[Spend] )
VAR _avg =
    AVERAGE ( 'Table'[Spend] )
VAR _actual = 'Table'[Spend]
RETURN
    SWITCH (
        'Table'[Spend],
        _max, "Highest Spend",
        _min, "Lowest Spend",
        _avg, "Average Spend",
        "Actual Spend"
    )

    2. Create a line chart(Axis: Time Frame  Legend: Type  Values: Spend)

    Best Regards

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Anonymous ,

Could you please provide some sample data(exclude sensitive) which refer in the line chart? And the legend value Lowest Spend, Average Spend and Highest Spend are from the fact field or some measures? If they are from measures, could you please provide the related formula? Thank you.

By the way, the label of the line chart changes according to the value and cannot be customized...

Best Regards

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi, thanks for replying.
Please, find the dummy data. The maximum is calulcated as Max(Spend), same for min and average. They are all calculated based on the spend column.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

    I created a sample pbix file(see attachment), please check whether that is what you want.

    1. Create a calculated column as below

    Type = 
VAR _max =
    MAX ( 'Table'[Spend] )
VAR _min =
    MIN ( 'Table'[Spend] )
VAR _avg =
    AVERAGE ( 'Table'[Spend] )
VAR _actual = 'Table'[Spend]
RETURN
    SWITCH (
        'Table'[Spend],
        _max, "Highest Spend",
        _min, "Lowest Spend",
        _avg, "Average Spend",
        "Actual Spend"
    )

    2. Create a line chart(Axis: Time Frame  Legend: Type  Values: Spend)

    Best Regards

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi, is there any way to achieve the below visual with the same dataset as above? Thanks again

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi, I noticed that if the value obtained by the expression for average is not in the present in the column Spend, it wont be displayed. Is there any way to fix this? ðŸ˜¥

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi Anonymous ,

        Since there is no corresponding average value in the table, the value "Average Spend" will not appear in Type field. We can go to Analytics pane and add an average line to the line chart as a workaround just as shown in below screenshot.

        Use the Analytics pane in Power BI Desktop

        Best Regards