Power BI Viz Tricks - Line chart target areas

Updated: Aug 4

A few months ago, I was asked to build a line chart with target areas, something like the chart below (which by the way will be our final solution!):


At the time I could not really find an easy way to achieve this... Until error bars came out!


Don't be fooled though, it's still a bit tricky to build a line chart like this, however I found it way easier now than before.


So, you must be thinking now "how did you do that?".


Well, let's find out!



1) I started out with my base chart, which is just showing Profit Over Time. I'm just using a simple sum measure for the profit, and a Date field from my data. Nothing too fancy.


2) Now we are going to work on the "Above Target" Area, or the one in green in the first image of this blog post.


To do this, we are going to use the Error Bars feature.

Even though the intention of this feature was not really for this kind of purpose, there is no limit to creativity in Power BI and you can, and should, be creative with all the formatting options in Power BI.


To start, we are going to the Advanced Options tab in the formatting pane, and then open the Error Bars section:



Once you open the Error Bars Section you will see something like this:




























For this example, we will be using a measure to define the Upper Bound and the Lower Bound for the "Above target" area.


I created two simple measures for this:

Profit Above Target Upper Bound = 50
Profit Above Target Lower Bound = 35

Your measures can be anything you want, in this case I use just simple numeric values but you can have some DAX for your measure as well.


Once I created these two measures, I just added them to my Error Bar Upper bound and Lower Bound. You should end up with a chart similar to the one below:


It doesn't really look ok yet does it? We are going to work on that in the next step!




3) Time for some formatting tricks! We are now going to do some changes to the looks of our error bars.


First, we are going to add an error band. This will add a shaded area just like the one we want:


Wait, this is still not looking like the target band we want... Let's do some more formatting!

Open the "Error band" section that we turned on, turn off the option "Match series color" (1) and then select a Band color (2).

You can change the transparency if you want, or the style of the band (with line or just filled).


Still not looking right... there is just one more step and we have our Target Area complete.

We just need to remove the error bars that are currently showing. To do this you just have to disable the bars in the "Bar" section:

And we have our Above Target Area!


Now let's move to a more tricky one, the Below Target Area...




4) The second target area will be a bit more tricky than the previous one.

The reason is that you can only use the error bars once for each measure. Because we used it already in our Profit measure, we can't just add a new error bar to that same measure.


So here comes the trick. We will add a new measure to our line chart, that will be exactly the same as our Profit measure. I called this measure something like "Total Profit Dummy".


This measure will allow us to use the error bars once more, this time to create our below target shaded area.

The legend of the chart will look a bit weird with two measures that are the same, and one called "Dummy", but we will fix that later.


Now we can use this new dummy measure to add a new error bar band!

If you go back to the Error Bars options of your line chart, you will see that you now have the option to select either the measure "Total Profit" or "Total Profit Dummy".


We already used the measure Total Profit to create the Above Target shaded area, so now it's time to use our new dummy measure, so let's select it and do the exact formatting process we've done before.


First, enable the Error Bar option:


Create the two measures for the Below target area:

Profit Below Target Upper Bound = 15
Profit Below Target Lower Bound = 0

Add the measures to the corresponding fields in the Error Bar options:



Enable the Error Band option (1), disable the option Match series color and choose your band color (2):

Lastly, disable the Error bars:


5) Tidying You probably noticed I have some labels that match the color of the targets. If above target the labels will show green, if below target the profit labels will show red.


You probably also noticed these labels are not showing when the values are in the "middle", meaning they aren't either above target or below.


How did I do this trick?


With more dummy measures!


I've added two more dummy measures. One to plot the line only if the values are above the target, and another one to plot the line only if the values are below target:

Above target = if( [Total Profit]>35