How to plot average line and upper/lower bounds on visual?

[tomi5]

Hi, I have the following sample data and chart. My actual chart is in power bi but this is just for demonstration.

I have put a line chart in power bi and added the maintenance costs as the y-axis and car age to x-axis and the car id to the legend. It gives me a chart like the one on the right hand side of the above image.

I want to add an average line and then an upper limit and lower limit lines (basically average +- 2 * standard deviation).

I am not sure how to achieve this in power bi. Because it won't let me use legend and multiple y-axis. Also the maintenance costs exists at a more granular level (for example if car id = A has $62 of maintenance in year 1 that could be made up of multiple rows of maintenance, so already some aggregation is present in the above sample data).

Can anyone help please?

[Greg_Deckler]

@tomi5 Have you looked at using the Analytics pane?

[tomi5]

Analytics pane plots a flat average line over the entire dataset rather than calculating the average for each year and then plotting a line based upon that. The latter is what I need so the analytics pane is not suitable.

[Anonymous · Accepted solution]

Hi @tomi5 

Thanks for the reply from Greg_Deckler .

If you can consider using Line and stacked column chart visualization, please refer to the following test:

Measure:

Measure = CALCULATE(AVERAGE('Table'[Maintenance Costs($)]), ALLEXCEPT('Table', 'Table'[Car Age(Years)]))

Output:

Best Regards,
Yulia Xu

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