NATIVE OVERLAPPING BARS in POWER BI // IBCS Style Variance Bar Chart Step by Step Guide

in this video we're going to have a look at how to use the new overlapping bars feature in parbi we're going to look through how we can improve our original solution with the overlapping bars with this new feature as well as some new improvements that you can think about implementing yourself such as changing the label placements uh changing colors as well as adding variance bars in your overlapping bar charts all of that and more so without further Ado let's get started hi my name is fernan and welcome to the solutions abroad YouTube channel recover tips tricks and best practices when working with powerbi upload new videos every week so make sure you hit that subscribe button and the bell icon to get notified when a new one is out so a few months ago I had the requirement where I needed to create an overlapping bar chart to show values uh for a previous and the current value sales and this is actually how that video looked like and at that time powerbi didn't have a native way of uh doing this type of visual so you would need to kind of have them side by side but not overlapping like this and while this feature or or kind of this method worked at the time we also needed to set up a lot of things to make sure that they are always aligned and that they are showing the correct values so a few things to note in this solution is that the fact that actually there's two charts here hidden they are just put on top of each other and then just kind of of using measures and some visual tricks to make sure that they're always aligned and they always have the same Heights so that when you're comparing between the two of them that they are showing the correct values now as of the February updates that was released this year this whole solution is now deprecated with a much easier to implement native solution in powerbi desktop so today I'm going to show you how you can do that fairly easily so this is a new template version of kind of of the same data sets that I want to use the the overlaps with so here we're showing the total sales for each of these different categories that we select we already have a few things set up in our semantic model here we have the sales which is just basically what we're showing in the bar chart here so we are showing the total sales for each of the months for 19997 of Applied already applied a filter here just to keep the bars a little bit shorter and then we also have a previous month sales measure here which is essentially just calculating what is the sales for the previous month and what I want to do first of all is to recreate the solution that we had from my previous video using this new native feature so what we're going to do is first we're going to bring in the sales pm here now I'm going to just make sure that it is on the left hand sides sales PM yeah and then I'm just going to adjust a few things first of all so we're I'm just going to change the colors just to make it easy easy to know which one is which so I'm going to make the previous month sales a lighter blue color so we can see exactly which month that is perfect so from here to enable the overlap you simply just go to the columns and select all if you have multiple values and then go to layouts so under layout you will now find this feature the overlap toggle which will let you now overlap your values so if you just adjust on the slider here how much you want them to overlap with so as you can see now if they belong in the same axis they overlap on top of each other which if you're comparing previous to current it's an easier way to to kind of read your data you can uh we've already adjusted The Ordering of our axis but you can also flip which one is on top uh using this toggle here at the bottom and as you can see it's that easy with a few simple clicks we were able to implement the same kind of solution you know without all of the hassle of creating overlapping charts on top of each other it's all Native in this one visual now let's have a look at a couple different ways that you can customize it to give it a bit more style or maybe just some options for you to consider if you want to use or upgrade this feature even further so the first thing that you might want to consider is or you might want to try out is you know trying to recreate ibcs style variance bar charts so at the moment here we have you will see the the actual value versus the comparing value and you will have a dark background for the the current and then a black outlined behind the overlapped bar uh as your previous and you know if you want to follow and make your reports ibcs compliant and I did cover this a SE several times already in this channel you can use SVG or other types of kind of visual options but you can do this also natively in powerbi at least parts of it so so to do that is actually fairly simple so from here you saw it already but basically it's all in the column section of the formatting so first of all here we're going to make it the this is the previous month sales we're going to make the fill color white and we're going to add a border here so the border is just black or gray but it just signifies that that is the previous month and then you will have the current month which we will toggle here and all you'll need to do is just simply just fill it with black so that's it so you know you now have sort of ibcs style kind of bar charts which was pretty easy to implement really the next thing that you might want to do is to enable the labels which we showed in our previous solution now we can do that actually here on the data labels now you will see there are some overlaps on the labels um because we have two axis axes here in the the Y AIS well but typically when you're showing comparison values like this you only want to be showing the current value just not to confuse uh your users on what the values you are showing so to do that you just simply disabled the previous month sales label when you enable dat data labels and then basically just showing the current values um you can increase the density if you see that for example some values are not being shown and that's purely because of the size of your font and the size of your visual if you increase that density it will just let you pack more labels in a tighter space in your visuals now you might you might find that in some cases you want your labels to always be at the top of the bars and not here like for example you can see here I believe this is August we want this label 7.4k at the top of the bars to show still 7.4k but not really uh on top of the bars themselves so to do that we're going to do a bit of Dax magic which uh we've already covered in the past before but it's going to be super simple so we're going to start by creating measure first of all we're going to name this one a label and we're going to just create a simple if statement so if the sales is larger than the sales previous month use the sales value else use the sales previous month so this essentially defines what height our labels are going to be so we're going to add this into our y AIS here but we're not actually we don't actually want to show this we just want it to show the label in the area that we want so what we're going to do is for the sales we're going to disable the label and we're just going to make sure that the label is the only one that is that is have the showing the the labels uh itself we're going to change first the column to to make sure that it's transparent because we don't actually want to show the label we just want to show the actual value and not the bar and then we're going to go back to the data label here um with the label series selected we're going to scroll down to the value and then we're going to change the field here that is here by default to show the sales so what it's done is what it's doing is essentially showing the labels for the current sales but on the right Heights that we we want so as you can see for example here in the same month August it's showing 7.4k but on top of the bar which normally would be here it's a little bit confusing and uh you will see that parbi does warn you of this when you kind of configure how the data labels work but we we are implementing it and we know how it works so we can just kind of disregard that but if you wanted a solution that kind of puts the label on top of the bars this is the easiest way to do it so let's uh disable the legend here just to make things less confusing something like this there we go now in the previous solution we also created or changed the color of the labels if the value that we're comparing is higher than the previous month or if it's lower and uh we can follow the same steps here so by by creating its own measure so what we're going to do is we're going to create a new measure here we're going to just name it color and we're going to do the same calculation so the same logic basically so if the sales is bigger than the previous month we'll make it green otherwise we'll make it red something like this and then what we're going to do is from the visual itself we're going to go to data labels with the label series selected you will look for the color of the value itself and we'll just change that based on a field value so we're going to use the color here and as you can see that changes the colors of the labels you can make it bigger or more prominent like this so it just shows you you know what those values are and if they're increasing or decreasing you can even go a step further and change these labels into variance percentages if you wanted to and if that is easier for your users but this is just how we're going to work in this example at the moment and then the last trick that I wanted to show is how you can show a variance bars which basically just shows it's meant to visualize kind of how big that difference is from the previous to the current and this actually is a solution from parb Park in YouTube and he's the one who came up with the solution so it's actually really really kind of cool Solutions so I'm going to leave a link to the original video that he created on this in the description box below so I'm going to show you how to do it from here so what we're going to do is we're going to create a new measure here we're going to create two new measures we're going to uh create one called Green Max and then we're going to create a red Max which is going to be the opposite so the idea with this is that we want to show a certain line if the difference is if the current value is bigger than the previous month we want to show the uh the value otherwise we want to show the the vice versa it sounds a little bit confusing but I'm going to explain it better once it's uh it's finished so let's do like this uh so now we've set up the measures we're going to go to the error bars and what we're going to do is we're going to select the first one is sales so apologize guys I had some technical difficulties my parbi desktop restarted and it didn't say my changes so I've just gone back to a uh version that I have um just and we'll just pick up where we left off earlier so where we left off is we created the green Max and the red Max Dax measures and we're now going to use the error bars to adjust essentially the the properties to add those variance colors onto our overlapping bars so to do that we just simply go to the error bars themselves so the first thing that you need to do is make make sure that you have the correct series selected here in your list so in this case we're going to start with the sales we're going to make sure that the error bars are enabled here and then on the upper bound you just need to make sure that you select the red Max and what you will notice it might not be that noticeable but when I zoom into to our bars you'll notice that it adds like this tiny line which only applies uh when the current bar is lower than the previous bar and the reason why we want that to only show when that when the current bar is lower than the previous bar is so that we can adjust the color of that bar and as you notice already the bar color on the error bars isn't you can't conditionally format it like you normally would so that's why we have to create this workaround to be able to adjust the colors so we have an upper Bound for this case and we use the if statements to check if that overlap is is on the right order if the current bar is smaller than the previous bar so uh from here we're going to just adjust a few things like for example um just make the width a little bit bigger maybe change the marker size the the tip points of the air bars to be none and then just change the Border color into zero just so that we don't have that border and now what it does is it basically just shows you you know that increase or decrease variance on the actual bars so let's do the same thing on the uh previous month so we'll select that that measure we'll make sure that it's enabled first of all then we'll click upper bound and select green mac so as you can see now it's added the the error bars but on the flip side on the ones where the current bar is higher than the previous previous bar so uh we'll do the same thing here so we'll change the color into green so we'll just select any green here marker size To None uh width uh we'll set it to 10 and then no borders and basically that's it so you have an ibcs style bar chart showing labels on top of the bars with the bar bar variants on the bars overlapping bars themselves and uh as you can see as you select different categories here it gives you a different analysis which is pretty handy and that's really it for this video I hope you now know how easy it is to use overlapping bars and how you can customize it in your pby airports thanks for watching in as usual give this video a like if you found it useful give it a dislike if you didn't soed to do better for next time ask your questions in the comment section box below so I can help you and you can help others if you like this video we have a patreon page where you can support the channel and get exclusive perks like Early Access demo files and credits at the end of these videos thanks again for watching and see you in the next one bye-bye

Channel: Solutions Abroad

Views: 3,275

Keywords: solutions abroad, power bi, powerbi, power bi tutorials, power bi for beginners, beginners guide to power bi, data analytics, dax, data modelling, data visualisation, business intelligence, how to power bi, power bi how to, power bi best practices, power bi tips and tricks, power bi standards, power bi patterns, power bi help, power bi tips, power bi 2023, power bi overlap, power bi overlapping bars, power bi variance, power bi var, power bi bar chart, power bi native

Id: ofrumZ69CdU

Length: 14min 50sec (890 seconds)

Published: Mon Mar 18 2024