Forum Discussion

NimaiAhluwalia's avatar
NimaiAhluwalia
Icon for Continued Contributor rankContinued Contributor
2 months ago
Solved

Line chart

Hello All,   I have a line chart which I want to create as below, in PBI:     I can create almost everything but not able to create the deciosn pointers, which are hightlited as the small ...
  • Ritaf1983's avatar
    1 month ago

    Hi NimaiAhluwalia 

    Yes, this is possible, but the decision point needs two calculated coordinates:

    X position: the negative value of Decision Point, because the chart uses days before the forecast period. For example, a decision point of 56 should be plotted at -56.
    Y position: the forecast accuracy at that specific point on the line.

    Because the available accuracy values are only at -90, -60, and -30 days, the Y value normally needs to be calculated using linear interpolation.

    For example:

    Decision Accuracy =
    VAR X =
    -'Forecast'[Decision Point]

    VAR Y90 =
    'Forecast'[Forecast Accuracy - 3 month ahead]

    VAR Y60 =
    'Forecast'[Forecast Accuracy - 2 month ahead]

    VAR Y30 =
    'Forecast'[Forecast Accuracy - 1 month ahead]

    RETURN
    SWITCH (
    TRUE (),

    X >= -90 && X <= -60,
    Y90
    + DIVIDE ( X + 90, 30 )
    * ( Y60 - Y90 ),

    X > -60 && X <= -30,
    Y60
    + DIVIDE ( X + 60, 30 )
    * ( Y30 - Y60 ),

    X > -30 && X <= 0,
    Y30,

    BLANK ()
    )

    The last condition requires a business decision. For decision points later than 30 days before the forecast, such as 15 or 5 days, you must decide whether to:

    keep the one-month accuracy value, as in the example above, or
    extrapolate using the slope between the two-month and one-month values.
    Native Power BI approach

    I would use two aligned line charts:

    The first chart contains the normal accuracy lines at -90, -60, and -30.
    The second chart contains only one point per country:
    X-axis: -[Decision Point]
    Y-axis: Decision Accuracy
    Legend: Country

    Add error bars to the second chart using small upper and lower bounds:

    Decision Lower =
    [Decision Accuracy] - 0.004

    Decision Upper =
    [Decision Accuracy] + 0.004

    This assumes the percentage is stored as a decimal, such as 0.84. If it is stored as 84, use approximately 0.4 instead.

    In the Analytics pane:

    Add error bars by field.
    Use Decision Lower and Decision Upper.
    Set the relationship to Absolute.
    Match the error-bar color to the series color.
    Hide the regular marker, axes, gridlines, background, and legend on the helper chart.
    Give both charts exactly the same X- and Y-axis minimum and maximum values, and place the helper chart over the original chart.

    Power BI supports field-based upper and lower bounds and formatting of error-bar width and endpoint markers.

    Do not add the decision points directly to the original line series. For countries whose decision point is later than -30, Power BI would extend the line from -30 to the decision point, which is not what the example shows.

    For a more robust solution, I would use Deneb and layer:

    a line mark for forecast accuracy;
    a short vertical rule mark for each decision point.

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.