Overlapping and Target Charts in Power BI

….using native visuals

Intro

Overlapping and bullet style charts are a good way to demonstrate whether a target is being met, or to show period over period performance.

Native BI visuals, on the face of it do not appear to provide the option of creating these. But with a few formatting tricks and creativity, we can create them together, both vertical and horizontal versions.

Vertical style bullet and overlapping charts

Horizontal style target line and overlapping charts

Sample Data

I have opted to use quite a simple dataset here for illustrative purposes. You can try this, or else any datasets with similar content.

This is six fictional schools with average testing scores between 2015 and 2022. I want to be able to show the current years testing score, with the context of the previous year score as a comparison.

I have pivoted the data to show schools, years and test scores, and also created a disconnected date table showing the years. You can download a copy of the base file here. It has the calculations below preloaded to make it easier to follow, but feel free to delete them if you wish to practise everything.

Visual Building Blocks

The basic content of these visuals is to show the current years average testing score, represented by a colour coded bar or column.

Then, to add that context, I want to use either a light grey bar in the background (overlap charts) or a short black bar (target charts) to represent the previous year. These will be the core components.

To create a bullet chart, I will add a series of gradient grey boxes.

Creating the Visuals

This requires a few steps, namely:

Disconnected Year Slicer
DAX Calculations
Visual Formatting
Error Bar Manipulation
Conditional Formatting

Disconnected Date Slicer

A disconnected year slicer is needed to select the year I want to show as my current year (aka ‘This Year’). If it is disconnected, it simply provides a reference year value for my calculations without filtering the test score table I am using and simplifies the DAX.

I simply drop the year in a slicer and apply single select formatting. I also filtered out 2015 as selecting it would not show any previous year data for context.

DAX Calculations

I will need several calculations, including multiple copies of the same calculation to achieve these visuals. These are primarily based around versions of:

Average Score This Year
Average Score Last Year

Score This Year = 
VAR _TY = SELECTEDVALUE('Year'[Year]) // disconnected slicer
VAR _tbl = FILTER(SchoolScores, SchoolScores[Year] = _TY)
VAR _Score = AVERAGEX(_tbl, SchoolScores[Score])
Return
_Score

Score Last Year = 
VAR _TY = SELECTEDVALUE('Year'[Year]) // disconnected slicer
VAR _LY = _TY - 1
VAR _tbl = FILTER(SchoolScores, SchoolScores[Year] = _LY)
VAR _Score = AVERAGEX(_tbl, SchoolScores[Score])
Return
_Score

Simply dropping these in a bar or line chart will not give me what I want (below). So I need to get ‘creative.’

I need to harness the power of error bars to create what I want, and I need to conditionally modify the DAX to show test scores when they above or below the previous year or the next year.

The below two DAX measures are to return two versions of this years score. H (high) is if this years score is higher than last years, and L (low) is if it is lower, otherwise return a zero.

Score This Year H = 
VAR _TY = SELECTEDVALUE('Year'[Year])
VAR _tbl = FILTER(SchoolScores, SchoolScores[Year] = _TY)
VAR _Score = SUMX(_tbl, SchoolScores[Score])
Return
IF (_Score > [Score Last Year], _Score, 0)  // if this years score is more
// than last years score, return the score, otherwise zero

Score This Year L = 
VAR _TY = SELECTEDVALUE('Year'[Year])
VAR _tbl = FILTER(SchoolScores, SchoolScores[Year] = _TY)
VAR _Score = SUMX(_tbl, SchoolScores[Score])
Return
IF (_Score < [Score Last Year], _Score, 0)  // if this years score is less
// than last years score, return the score, otherwise zero

I create the same for last years scores:

Score Last Year  H = 
VAR _TY = SELECTEDVALUE('Year'[Year])
VAR _LY = _TY - 1
VAR _tbl = FILTER(SchoolScores, SchoolScores[Year] = _LY)
VAR _Score = SUMX(_tbl, SchoolScores[Score])
Return
IF( _Score > [Score This Year], _Score, 0)  // if last years score is more
// than last years score, return the score, otherwise zero

Score Last Year  L = 
VAR _TY = SELECTEDVALUE('Year'[Year])
VAR _LY = _TY - 1
VAR _tbl = FILTER(SchoolScores, SchoolScores[Year] = _LY)
VAR _Score = SUMX(_tbl, SchoolScores[Score])
Return
IF( _Score < [Score This Year], _Score, 0)  // if last years score is less
// than last years score, return the score, otherwise zero

I also want to specify a base level 0 as a DAX measure to create a base reference for the error bars. You will see it applied below:

Base = 0

Visual Creating and Formatting

Now, I can create the first visual, the colour formatted overlapping chart

First, select a line and stacked column chart, then drop Score Last Year H and Score Last Year L in the columns, and School in the x axis.

Next drop Score Last Year and then Score This Year H and Score This Year L in the lines.

Click on the ellipsis at the top right and sort axis by school and ascending.

Ok, this looks a bit messy, but now I can apply some formatting magic!

Basics first:

Remove legend
Change main title and axis titles
Change columns to the same light grey colour
Remove gridlines
Increase inner spacing to 40–50px (depends on size of visual you use)

This will give me the below….

Next, I go to line formatting and I change the stroke width to zero, and make the lines disappear. Don’t worry, there is a good reason, I am not going crazy!

Error Bar Manipulation

This is where error bars come into play. The lines I have just hidden effectively act as reference points for my error bars.

I go to error bars and initially select Score This Year H and enable error bars. For the Lower bound, drop in Base (which is equal to zero) and the Upper bound as Score This Year H, and enable Bar.

This gives me the below result, and provides a bar for the two schools where this years score was higher than last year (H).

The reason I need to split out high (H) and low (L) scores, is that error bars do not allow colour conditional formatting, therefore I need to manually apply, as I will show now.

So, for the bar, I select a bar colour, increase the width to 10, change the marker to None, change the Border color to white, and the Border size to 2px. You can make this whatever appeals to you, I like the visual separation it provides.

Now, I replicate this for the low (L) scores. I apply settings to the low score, and use Score This Year L as the upper bound. I then apply the same formatting to the bars as above, but use orange to represent a lower score (feel free to use whatever colours you wish).

This provides the first chart, which I can now fully use.

The remaining visuals follow similar principles. I will recreate them below, and refer back to any repeat steps for brevity.

Colour Formatted Bullet Chart

This visual requires some additional simple measures to create the grey gradient.

I create 5 x DAX measures (S1, S2, S3, S4 and S5) each equal to 20, and combined to add up to 100 (my max score). If you want 4 gradients or 10, adjust the values as needed.

S1 = 20

Now, I create the visual. Again, select a combined stacked bar and line chart. Drop the Sx measures in the columns, and Score Last Year, Score This Year H and Score This Year L in the lines, as below.

Again, looks messy, but similar formatting will make it look better:

Grade colour the columns
Adjust inner spacing (40–50px)
Title rename
Remove legend
Adjust axes

Next, I will again turn All the lines to zero width.

However, I will use the line markers to create the black horizontal bar which represents last years scores.

Turn on markers, then select Score Last Year, and apply formatting. Select Score This Year H and L, and turn Show marker to off. You should get the below effect.

Next, I apply the same steps for the error bar application and colour formatting I performed above, and I get the below results, giving me the bullet chart effect.

Horizontal Charts

The horizontal charts pose a problem if I want to have colour coded bars.

Why?

because there is no horizontal version of the combined bar and line chart that I can use, which means I cant use the ‘hidden lines’ as reference points for error bars and markers.

This has been a bit of a ‘head-scratcher’ for me for the last few weeks, but I have managed to come up with a solution. It is a little more complicated and you may notice the slight ‘offset’ in the bar alignments, but I believe it is the closest I can achieve using a native visual with the current formatting allowances.

Ok, let’s do this.

So, what is the trick? Well, it is using a clustered bar chart with 4 horizontal bars for each school.

DAX Calculations

I need 2 versions of the Score Last Year measure, so I replicate it as per the below. These two measures will act as reference points for either a blue (Score Last Year) or orange bar (Score Last Year2).

Score Last Year2 = 
VAR _TY = SELECTEDVALUE('Year'[Year])
VAR _tbl = FILTER(SchoolScores, SchoolScores[Year] = _LY)
VAR _Score = SUMX(_tbl, SchoolScores[Score])
Return
_Score

Then I need 2 more measures which are effectively the same as the H and L measures. They are conditionally formatted to either return last years score or a zero, depending on the condition.

The order in which the bars are placed in the chart becomes important. This is explained further below.

Score Last Year3 = 
VAR _TY = SELECTEDVALUE('Year'[Year])
VAR _LY = _TY - 1
VAR _tbl = FILTER(SchoolScores, SchoolScores[Year] = _LY)
VAR _Score = SUMX(_tbl, SchoolScores[Score])
Return
IF( _Score < [Score This Year], _Score, 0)

Score Last Year4 = 
VAR _TY = SELECTEDVALUE('Year'[Year])
VAR _LY = _TY - 1
VAR _tbl = FILTER(SchoolScores, SchoolScores[Year] = _LY)
VAR _Score = SUMX(_tbl, SchoolScores[Score])
Return
IF( _Score > [Score This Year], _Score, 0)

So, I now have the four measures to create the four bars

Horizontal Overlapping Chart

I drop those 4 repeated measures in the clustered bar chart, and I get the below. It is important to remember the order, and we will use each of these 4 bars as reference points for the error bars.

If you see below, although I dropped in four bars, each school only has three bars visible. That is because one of conditional formatting of Score Last Year 3 and 4.

Following the logic, if the score last year > score this year, I will get a red bar on the bottom. If score last year < score this year, I will get the sky blue bar on the top.

Thus, when I use Score Last Year as the reference point for the blue positive score, it will be surrounded by one bar on the top and bottom. When I use Score Last Year2 as the reference point for the orange negative score, it will again be surrounded by one bar top and bottom. This will give a consistent width and height for each.

If you are confused, the below work through should illustrate it, then maybe come back to read this again.

What I will do now is colour all as grey and apply basic formatting similar to before

These will act as my background. I will now apply error bars and use Score Last Year for my positive results (blue) and Score Last Year2 for my negative results (orange). Score 3 and 4 will always be grey whether in positive of negative scoring, and therefore provide that grey background on both sides of the colour bar no matter what the value is.

First, I will apply the positive scoring in the error bars as below, using Score This Year H. I turn on the bar, and apply a width of 8 and a white border of 1px. Again, you can adjust to your own preference.

Repeat the same steps for the scores less than last year, this time using Score Last Year2 as the reference. If you zoom in very closely you might see a little difference in spacing between bars of each school, but I think think it gives a good representation.

Horizontal Target Style Chart

Ok, now onto the last version, using a target line along with the colour codes bars.

Again, if you look closely, you may notice a slight uneven spacing between orange and blue bars. If you have OCD you might not be able to accept this, otherwise it is a good option in lieu of using a third party app to create something like this.

But who knows, the new core visuals team may introduce a horizontal version of the combined bar and line, which would allow us to get a more even result.

Enough of the commentary, let’s finish this.

Ok, I will go back to the step in the previous chart where I had four grey bars as my base.

Then I will change their colour to white (or whatever background visual colour you use. This might mean it is difficult to use this if you use a transparent background unless you can match the colours.

Next I apply the same error bar formation for Score Last Year and Score Last Year2 to give the blue and orange bars.

Now, for the black bars. Instead of a line mark, I will use the outer error bars to create a combined line mark. For Score Last Year3, I use the same Upper and Lower bound limits. This provides a small mark line as shown below.

The small mark is not much good, but I can adjust its formatting to make it more prominent, increasing the size to 10. However, it is skewed, and doesn’t cover the entire width of the bar.

To remedy this, I repeat the same process for Score Last Year 4, I will get two marks that will then “touch” and give the appearance of a single black bar, like the below.

Again, if you look very closely, you may notice that slight offset or varying space between categories. Depending on the size of the visual you are using, you may play with some of the bar spacings, error bar sizes and marker sizes to get your feel just right.

For reference, my visual above is 370H x 490W.

All done

So that’s all the visuals complete, vertical and horizontal.

All of these visuals would be a lot simpler to create if I didn’t want to apply colour formatting to the bars, but I think it is worth the extra steps to give that visual indicator and effect.

You might want to experiment and see if you could create a KPI visual with this type of format, using the title and subtitle custom formatting.

Horizontal target and overlapping charts

We will see what the updates to the core visuals bring in the next year, and see if it makes creating these types of visuals much simpler.

Thanks for reading — as always feel free to provide feedback or let me know if there are other types of visuals you would like to see created.

The contents of external submissions are not necessarily reflective of the opinions or work of Maven Analytics or any of its team members.

We believe in fostering lifelong learning and our intent is to provide a platform for the data community to share their work and seek feedback from the Maven Analytics data fam.

Happy Learning!

-Team Maven