I Added One Thing to a Bar Chart and My Client Was Speechless

When we are analyzing data over time then usually we are interested in seeing the difference between the starting period and the ending period. So if we have the last three years that would be

from 24 to 26. Sometimes however the user wants to change that comparison for example 22 to 24 or 25. Well and that's exactly where it gets very tricky from a

UX standpoint because how can you visualize that? Well, for that I have

visualize that? Well, for that I have this chart over here where the user can choose a starting year, ending year and the difference is nicely visualized on the right hand side of a chart. Now,

this is a super interesting example from a UX standpoint, but it's also full of technical tricks. So, let's dive in.

technical tricks. So, let's dive in.

I hope that you're ready for this one because from a technical point of view, it's not an easy one. However, once it works, it's really satisfying. So make

sure to download the file using the link in the description below and then you're ready to go. So let's go over all the different steps. Let's first have a look

different steps. Let's first have a look what kind of different components we have to build into this column chart.

Now first of all we have a slice here where the user can choose a starting year and an ending year for periods that we want to compare. Now then we have some conditional formatting applied. So

you see we have a different color for the start and ending year. Then we have over here lines that go to the right hand side all the way to the right where

we can then see the difference nicely visualized. So there's this little

visualized. So there's this little connecting line plus a data label that shows the absolute difference and the percentage difference and we have to figure out how we can well put it actually here on the right hand side

because well normally the years go from 2020 to 25 and there's no space on the right hand side. Well, as a first step, we have to figure out why that slicer that we have at the top doesn't filter the column chart below it. Because

usually a slicer filters the charts, right? But in this case, you see I have

right? But in this case, you see I have over here 24 as the ending year, 21 as the beginning year, but 2020 still shows up and 25 still shows up. How is that

possible? Well, for that we have to go

possible? Well, for that we have to go to the data model. So, let's have a look. Now, over here, you see I've built

look. Now, over here, you see I've built a table dim slicer which is not connected to any of the other tables. So

if I go to the table view then you see dim date slicer just returns a list with all of the years that we have in our dim date table. And how can you do that?

date table. And how can you do that?

With a simple formula just like this one where I use values on the year field in our calendar. Now when you have this

our calendar. Now when you have this then you can build a slicer. It can be just an old slicer which we can put over here on top of a visual. And then I'm

going to take from dim date slicer the airfield. Put it on there. And boom, we

airfield. Put it on there. And boom, we already have it. However, I would simplify it a bit. I would rid of the slider. Also, the header we don't really

slider. Also, the header we don't really need. Maybe I'd make the text a little

need. Maybe I'd make the text a little bit smaller. And then I can resize it

bit smaller. And then I can resize it just like this. Perfect. Put it over here. Maybe we also don't need the

here. Maybe we also don't need the background. Let's turn that off. All

background. Let's turn that off. All

right. And then I want to have the text compare right in front of it, which we can do with a normal text box. Now, the

next thing that we need to make sure of is that when we change the years, it actually does have a filter into action with the column [snorts] chart below.

Now, it will not change anything.

However, we still need to know in this visual context what years are selected.

All right. So I'm going to go here to format edit interactions and make sure that this one is set to filter. Okay.

Now the next thing that we can do is to well create a little bit of space there on the right hand side and apply the conditional formatting. Now let's first

conditional formatting. Now let's first create that space on the right hand side. Now if we compare what we

side. Now if we compare what we currently have to the end solution you see here we have much more space on the right hand side but there's no formatting feature that lets us create

that space. Now the trick here is to

that space. Now the trick here is to create a placeholder. Now what comes after 25? Well, the next year is 26. But

after 25? Well, the next year is 26. But

of course, I don't want to show year 26 there. So yes, we can extend our date

there. So yes, we can extend our date table with that extra year. But well,

then we would have to have an overlapping shape on 26 so that it doesn't show. But I find that a little

doesn't show. But I find that a little bit of an ugly trick. So let me show you how I would solve it. Let's go back to the table view and select our calendar table. Now over here you see a standard

table. Now over here you see a standard way of generating a calendar table or my dim date table with two small changes.

First of all I've changed the ending point. Now you see I first figure out

point. Now you see I first figure out what is my max year in this data set.

Now in this data set it goes to 25 and then I make a small adjustment. The due

date adds one year. So if my data set goes to 25, my date table will go to 26 as you can see over here. Then a second

change that I made is that have one extra column which is called year last year blank. So for those dates where we

year blank. So for those dates where we have the year 26, I have a year column that just returns 26. But I have also an extra column that returns a blank for

only that last year. for all of the other years. Let me just scroll down. It

other years. Let me just scroll down. It

does show the year. All right. Now, why

is this important? Because if we go back over here to our chart, we can take that last year blank column and what will

happen is nothing unless you right click on that field and say show items with no data. We have space there on the right

data. We have space there on the right hand side. Just make sure also that that

hand side. Just make sure also that that last year blank column has as a sort by column the year. Okay? And then sorted of course in ascending order. Now

another thing that's important is that that last year blank column is a text column. You cannot mix variant data

column. You cannot mix variant data types in one field. And therefore I've added this little text string there to convert the years to text. Okay. So now

that we have this space on the right hand side, we have the slicer that is disconnected. The next thing that we can

disconnected. The next thing that we can do is apply conditional format. Now for

that I've already set up a measure which is really not that complex. Now over

here this measure returns a one or a zero if the year that we have on the horizontal axis is equal to the minimum or maximum year selected in the slicer.

Now also here you have to watch out with the data types. So I wanted to solve it in the measures and also here I've added a little text string to it. Of course

you could also go to your D date slicer table change the year field to text there would also work. All right so just keep that in mind that it has to correspond. Now that we have this

correspond. Now that we have this measure we can go to the column chart let's select it formatting and then here for columns I'm going to apply conditional formatting to the color. I

want to use a field value and my measure is called CF which I cannot select. Ah,

yeah. I want to use rules instead. All

right. So, I'm selecting my CF measure and this one is going to be equal to one. And if it is, then I want to have a

one. And if it is, then I want to have a different color. Let's go for this one

different color. Let's go for this one or maybe that one. Now, you see that the conditional formatting is working. We

have here the year 2020 highlighted. The

26 is in the future. And therefore, you might also want to consider putting a visual level filter here on the airfield if you don't want the user to be able to fill out 26. But let me just change it

to 25. Yeah, there you go. All right.

to 25. Yeah, there you go. All right.

So, our conditional highlighting of the column chart is working using a disconnected table, but still having a filter interaction to the column chart.

Conditional formatting now and we have a little bit of extra space there on the right hand side of the column chart.

Quite a few tricks already. Now the next thing that we need to do is draw two horizontal lines. One that goes from the

horizontal lines. One that goes from the starting year all the way to the right and one from the ending year to the right where we have that extra space.

Now for that we need two measures. Let

me show you. Now that looks like a long measure. However, it's actually not that

measure. However, it's actually not that complex. Here we have sales actual.

complex. Here we have sales actual.

Okay, our main measure and we need to use a calculate function to change the filter that gets applied. Now what do we need to do first? First of all, I want

to remove any filter that we have coming from the horizontal x-axis where we have the year last year blank field. And then

I want to replace that with a new filter where we set the year equal to the minimum year in our slicer. Okay? So

that we have the sales actual for that minimum year. Now we only want to show

minimum year. Now we only want to show it where we have that blank space in our visual. So therefore I use an if

visual. So therefore I use an if function where I check the year last year blank value and if it's equal to or

bigger than that minimum year selected in the slicer in this case 2020 then I want to draw a line that returns that

minimum value all the way to the right and same for the other measure. So here

we have max year line which does exactly the same thing but then if it's equal to or bigger than the maximum year. Okay.

Now let me show you these two in action.

Now I'm going to take many year line put on the line yaxis and max line put it also on the line yaxis. So you see I'm using not a normal column chart but a

line and plastic column chart. So that

looks good. We have the two lines.

However, and we need to clean up the formatting by turning off the legend and we don't need that. Then also here we have all these data labels showing.

Let's turn them off for the minia line and maxia line. Okay. Then the lines themselves. Let's give them a different

themselves. Let's give them a different color. Right. So over here I want to

color. Right. So over here I want to have them first of all a little bit thinner. And I don't want them to be in

thinner. And I don't want them to be in different colors. So menu align. Let's

different colors. So menu align. Let's

select that one first. And let's go for the dark color or light gray color, whatever you prefer. And now we have these two horizontal lines. Now let's

actually check if that works. So if I change the year to 21, boom. Yeah, the

line now starts at the year 21. And what

if I change this to 24? Boom. Perfect.

All right. So this is nicely working.

Also, that effect looks really cool.

Short side note. If you want to be up to date on the latest features in PowerBI, then you really need to know about UDFs, userdefined functions, which are changing the way that we write, maintain, and share DAX throughout all

different kinds of PowerBI reports. It's

not necessary anymore to repeat similar DAX logic over and over again now. So,

it's really a gamecher within PowerBI.

That's why I decided to record a whole training on it where I show you all the best practices of how to use them, plus practical use cases. And of course, you get a whole UDF library that you can use

directly in your PowerBI reports. Now,

if that sounds interesting, then check out the link over here. And of course, for those people that are early, I have a special discount. Let's go back to the video. All right. So, again, one step in

video. All right. So, again, one step in the right direction. The next thing that we're going to do is connect these two horizontal lines with a vertical line.

Now, the thing is the line chart is always showing the development with horizontal lines. We cannot draw

horizontal lines. We cannot draw vertical lines. But for that we're going

vertical lines. But for that we're going to use arrow bars. Now another thing is we only want that arrow bar to show up

from this point onwards. So for the year 26 our placeholder right and to do that we need another measure. Now let me show

you that measure. So over here we have arrow bars positive and this measure you need basically twice or positive values

and negative variance differences.

if you want the connecting line to be in a different color. If you just want to have one color, then you can basically leave out this condition and just check

if we have that placeholder here. If so,

max your line. Okay, so a very simple measure which we are going to put onto our visual and we can just put it here on the line Y-axis. It just turns a

little dot there for our ending here.

Okay, now we can go to the formatting options to first of all change the formatting a little bit. We don't need the data labels. So, error bar is positive. Let's turn that one off. And

positive. Let's turn that one off. And

that little dot that you see there, it's not a marker. It's just that we have a line with just one data point. And we

can just change the color, right? So,

for example, a nice green color. All

right. Now, if you want, you can play around with the markers, of course. So

we can turn the markers on and then we can make it also a little bit bigger or yeah go for a different shape. Okay. So

with that we can now go to arrow bars and here I want to add an arrow bar to arrow bars positive to that series.

Enable it and here I want to connect it to the line below it. So that is the minear value. So let's look for the

minear value. So let's look for the minyear line. Put it on the lower bound.

minyear line. Put it on the lower bound.

Now we have the connecting line. I go to bars, turn that one on. Bar color is also going to be green. Make it a little bit thinner by setting the border size

to zero and markers. And you can turn Okay, good. Now we have that connecting

Okay, good. Now we have that connecting line. All right. So now we have that

line. All right. So now we have that connecting line. The next thing that we

connecting line. The next thing that we can do is just put a data label right above it. However, it's data label with

above it. However, it's data label with multiple lines. So if we go to data

multiple lines. So if we go to data labels, let's open up arrow bar positive is selected. I'm going to show that

is selected. I'm going to show that series. Now here we want to have

series. Now here we want to have multiple lines and you have to be explicit. So layout single line

explicit. So layout single line multi-line needs to be selected. Then we

can go to value and then here we can change the arrow bar positive to let's see where do I have it data label. And

here we're going to have two parts the title part and the value part. Now I'm

going to choose over here my label title. Now of course I could also have

title. Now of course I could also have used the actual title formatting option.

However we have title value detail that lets us split basically the different parts from each other. Then we can go to detail. Now let's just put the value on

detail. Now let's just put the value on this one then. So over here and well that already shows what I wanted to show. However, we can make it look a

show. However, we can make it look a little bit nicer by going here to the background. Choose that green color.

background. Choose that green color.

apply a little bit of transparency. And

then I probably would make the percentage maybe a little bit smaller just like this. And then here the value above it that one we can make dark

green. Maybe you want it bold. Maybe you

green. Maybe you want it bold. Maybe you

want it bigger. Okay, you get the idea.

All right. So play around with the formatting there. And that's it. It is

formatting there. And that's it. It is

working almost because the difference is not always positive, right? So if we go here from 21 to for example 23 then

now it's messed up. Okay. So now it doesn't show and the secondary y-axis also shows which actually shouldn't show. So over here let's turn that one

show. So over here let's turn that one on and off again. Okay.

What now? Now to set this up for when the variant is negative is exactly the same. So if I show you over here the end

same. So if I show you over here the end result, you see we have arrow bars negative also on line Y-axis. Then if we go to the formatting options arrow bars

then over here apply exactly the same settings to those arrow bars and also over here for the data labels arrow bars negative. Here you see our value and

negative. Here you see our value and detail background exactly the same as before. And that's it. That is the whole

before. And that's it. That is the whole trick. Well, I should say bunch of

trick. Well, I should say bunch of tricks because it's not an easy one to build, but very satisfying when it works, isn't it? All right. Now, if you want to watch more videos just like this one, check out these videos over here.

If you want to know how I build my PowerBI reports in real life on my different consulting projects, then check out my PowerBI design transformation program where I share all of my tips and tricks. All right. Now,

thank you for watching and see you in the next video.