.png)
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 = 35Your 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 = 0Add 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, [Total Profit])
Below target = if( [Total Profit]<15, [Total Profit])Next, I've added these two new measures to my line chart:
This is now looking a bit weird isn't it? Let's fix it in the next step!
6) In this step, we are going to do some clean up for our new measures.
The first step will be to add some markers to our line chart. I've also increased the size a little bit:
I've applied these changes to all measures, but you can disable the markers for specific measures. I disabled the markers for the Profit measures and let it enabled for my new two metrics "Above Target" and "Below Target":
While you're in the Markers section, change the color of both the "Above Target" and "Below Target" markers to green and red:
The labels of our above and below target markets are still missing, so we will now add them:
The result of enabling the Data Labels option in your line chart will probably look a bit weird.
This is because we currently have 4 measures in our line chart, so it's showing the labels for all of them. We will disable the ones related to the Profits metrics, and leave only the ones for measures "Above target" and "Below target":
One last formatting activity in the labels before we tidy everything up!
In the Data Labels Section, you can format your labels using the options under "Values".
I just added some bold formatting on the text, increased it to size 10 and then just choose a color to match (red and green):
4) Tidying things up a little bit now!
The first thing we are going to do in this last step is removing the extra lines we don't need.
We just need our line chart to show the original line for the Profits values, and not the extra 3 dummy measures we added.
To do this, go to the Formatting tab of your line chart, and in the Lines section make the option Stroke width zero and the line will just magically disappear! Do this for your dummy measures and for the Above Target and Below target measures:
We are almost there!
If you hover over your chart, you will see that the bound values for the Errors Bars will show in your tooltip. You may choose to leave it like this, but I think this is just adding clutter so I'm going to remove these tooltips.
To remove the Error Bar tooltips, go back to the Advanced options of your line chart, then go to your Error Bars section and disable the tooltips for both the Profit and the Dummy Profit measures:
The tooltips for the "Above target" and "Below target" measures will still show though.
You can fix this by using a custom tooltip, maybe for another blog post!
7) The final touch!
Even though it's pretty similar, the chart is still not exactly the same as the one in my example.
Here are the list of the final formatting touches I did:
- Disabled chart legend
- Changed the title to "Sales Profit Over Time", changed text type to Segoe UI Light, increased font to 18
- Removed titles of the X-Axis and Y-Axis, and then removed Y-Axis:
- Changed line color and stroke width
And this, my friends, is how I went from a very basic line chart, to a line chart with custom data labels, target bands and a good formatting!
Full video tutorial on how to build this solution below!
Interested in learning more about Power BI Report Design? Then the Power BI Report Design Bootcamp is for you!
