How to show OVERLAPPING BAR CHARTS in Power BI // Show and Compare Variance

in this video I want to show you how you can create something like this where you have a bar charts on top of a bar chart comparing two values against each other so you can see the previous month's values beneath as a lighter color you can see the variance in colors with some arrows pointing up or down and all of this Dynamic by being able to select which month you're looking at and which month is comparing against 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 where we cover tips tricks and best practices when working with power bi 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 this idea came as part of a requirement from my current work at the moment where I had the needs to show the difference between two different sets of value but not side by side but rather putting them overlapping each other so that we can easily show that the previous one is at the back which is in a lighter color and then the current value being the solid column at the front now power bi doesn't natively support this in a single visual however if you really think about it what it is is essentially just two bar charts on top of each other the only complicated part about this implementation is to make sure that's no matter what selection the users make within your page that these two charts stay in sync so I have a few tips that you can follow to make sure that this will always work for you so to start with let me introduce you to the data model that we're working with for today it's a subset of the Northwind dataset which is a company that sells grocery Goods internationally we just have a few tables here we have the orders table which is the list of orders and when they were ordered the actual order details so what was ordered how many and how much we have the products table which is the list of those products and their product ID and then the categories of those products I've already created a calendar table which is a very simple one we have just the calendar with dates that is the range of our our data model and then we have a calculations measure table which already has two measures in here so just to show you we have the sales which is obviously just calculating the sales amount by multiplying the unit price and quantity from the order details table along with that I've also created a previous sales measure which is just using the previous months it always calculates the previous months within our calendar table so that way we don't really need to worry about what to show on that bar chart at the back so the first thing that we'll need to do is to obviously visualize all of our different categories and their sales I'm going to change this into a bar chart like this and I wanted to be able to select and change the month that we are looking at so for that we're just gonna go to the calendar table bring in the month in a separate visual here and I'm going to convert this into a slicer and I'm going to change the settings for this to be a vertical lists just to make it easier to see so now as you can see it's giving me the total sales for each of those months so as I select November December there we go so I'm just going to make this a little bit bigger so just to show you I'm gonna add the previous sales as a separate axis here just to show you how the previous cells look like so the previous cells is the dark color here and it's showing us so whatever we have selected here is showing us the sales and the previous month sales based on what we've selected so if we've selected November 1997 previous sales means that that's the sales for October 1997. and want to be able to show this sort of bar beneath the original bar the sales bar so that it's obvious that it's the previous month's sales now we're not gonna do it by using side by side here but we're gonna have another bar chart underneath this bar chart which will show that value so just to show you how the previous sales values look like so there are a few things that you need to bear in mind if you're implementing something like this especially if you want to make sure that your bar charts are kind of static or that they are always in sync the first thing is that you need to bear in mind that the number of values that you have in your category must stay the same regardless if it's a previous sales or the current sales so at the moment this bar charts when there is data it will show everything but if you have months where there are no data it will show you no data for those categories so what you want to make sure that you do is when you go to your build your visuals on your x-axis you right click on that category in your x-axis well and toggle show items with no data so that way even if you select any months that have no data value it will always show that category now you might want to use this in combination with the filters pin and this is just to make sure that you're always showing the correct number of categories within your bar chart the next thing is the Sorting of your bar chart so by default it's sorting your bar charts descending based on the total sales for that category now if if you are looking at a different month the previous sales would be looking at a different month for example it will be sorted a different way so you want to make sure that both the current month sales and the previous month sales are sorted in the same order so you can use either custom ordering which is something that I covered in a previous video if you wanted to control it in a more custom manner but in this case we're going to keep it simple so what we're going to do is we're going to sort it alphabetically so we're going to say custom sorting ascending and then sorted by category name that way it's sorting those categories alphabetically so next thing from here is obviously just to adjust the formatting settings that we have in this bar charts there are a few things that we might want to adjust so the first thing is to obviously make sure that the background is disabled just so that we can have the previous month sales underneath it and we can see that if you have a few things here like the title axis if you don't want those I typically turn them off because they are pretty self-explanatory I change maybe change let's go and change the color of those categories and on the columns as well if we go to spacing we want to give some space to for the bars beneath so we're going to create a bigger inner padding so we have that extra space that we can show that the previous month's sales and I think we are pretty much ready so what we're going to do from here is I'm going to copy and paste so we have a duplicate value like this and then we're just gonna offset it to the side like this there we go so we're gonna control this and just make sure that it's at the back so I'm gonna go to selection here and this must be you'll see that there are two values now so one that is offset and one that is our current one I'm just going to make sure that the previous one will be at the bottom of the ordering so this makes sure that it controls the Z order which is to be at the back and you can see what is in the back because both of them have transparent backgrounds so we removed that from the beginning so now we're just gonna adjust several things here the first thing is to adjust the color of that bar and they're all the same values at the moment because we need to change that into previous cells so maybe let's let's try to do that now just make sure that you're selecting the right right charts here so we're going to change this y-axis to use previous sales which as you can see now is a little bit different from the current sales and now we will adjust the settings just because we can see that they're obviously duplicating because there are two charts now so we need to hide the sets of values at the back without really kind of formatting and changing the size of them so the first few things that we'll need to change are the X and Y axis so we're going to change them all to be the same color as the background not necessarily removing them because it will adjust the height of our bars so y-axis as well y-axis values and color like this and then we'll just need to make sure the title also is white so we don't see that overlapping so here we go it looks like we're almost done actually maybe the grid lines as well just to hide that I can find it there we go there we go so there you go so you have now a bar chart with the current sales and then the previous month sales behind it as a lighter color and that changes based on what you have selected here so it might look correct but if you notice that the axis here on the left hand side that also changes based on what is the maximum value that you have in that chart so although they might look correct the the size or the how big the axis is is currently automatic so it adjusts automatically based on the highest sales that you have in your chart now we do want this to be automatically changing because obviously as we change months the size of the bar chart should also scale however we just need to make sure that when it does automatically scale that they are kind of the same they are the same size both the chart at the front and the chart at the back so what we're going to do and actually where you update that where you make sure that is similar is on the y-axis range here so as you can see here it's at the moment automatic but you can create some conditional formatting to adjust that dynamically and that's what we're going to do so we're going to go to data here and create a new measure we're going to name this one Max axis we're going to create a few things so we're going to create two variables here which is the current the highest value in the current month sales that we've selected so I'm gonna use max X and then we're going to say all selected from the categories and then just give it sales I'm going to put it in a table so you can see what it does but basically what it does is it makes sure or it finds out what is the highest category sales that is currently selected so I'm gonna drag in category here sales and Max axis so as you can see what it's giving me is which category has the highest sales in this kind of context in this table so 28 000 is the highest now we want to also find what is the highest for the previous month sales so we're gonna say bar previous and we're just gonna copy this and change this one to the previous sales so now we need to find the highest value between these two sales measures and that's what we'll use as our maximum in our y-axis range so we're going to say if current is greater than the previous use current else use previous but this will give us whatever the highest value is across all of those sales and previous sales and then we're gonna add five percent on top of that and the five percent ensures that there is a bit higher than what is the highest value in our sales it doesn't make any sense but it's just to add some padding before above the highest sales so that there is a bit of space for data labels so here is giving us 37 000 at the moment which is at the moment as you can see well you can't you can't really see it but if I put previous sales here so as you can see here for beverages even though 35 000 previous sales is higher than 28 000 it's showing the opposite way here in our charts so this is why we need to update this Max axis for our charts so we're gonna select the previous sales first of all change that Max axis to use this value that we've just created the measure and then we'll select the current sales as well change the max and use that so that is a more accurate picture of what the scale actually looks like so you have 35 000 on beverages on the previous month compared to the current which is 28 000 so it's showing you the right scales lastly you might want to show some variants on the data labels of your bar charts so we're going to do that quickly so because it's not really that complicated and we're just gonna use Dax to add those arrows going up or down so we're gonna go and create a new measure here I'm gonna just call it variance like this and we're going to create a new variable here called variance and that's because we're gonna keep reusing this one and this basically just calculates the sales against the previous cells to get the percentage difference and then we're gonna use first of all the variance and then we're going to add the space in between I'm going to say an if statement if variance is greater than zero then we need to use an up Arrow I used Windows dots to bring up this board which I also covered in a previous video we just need to make sure also that the formats of the variance is a percentage format because it will just show decimal if we just leave it like that we're going to use the format function to format that we just say hash and percentage so now that you have that as a measure you can use it as a custom label so we'll select the chart that we have here go to data labels and then go to values enable custom label and add that variance measure so it will show you if the value between previous to the current month sale is going up or down which pretty much does and it looks like it's it's sort of correct the last thing is to add a bit of color in this data labels to kind of make them pop out and really show if it's up or down red or green so we're going to create a very simple measure here just gonna name it color and we'll say if the sales is greater than previous sales then make it green because it's a positive change otherwise it's red and then go to the charts go to the data labels under color click conditional formatting change it to choose and use field value and then we'll just pay color and there you go so now as you updates it will show you how much of a difference those sales are in between those months and what the variance difference is and that's really it for this video I hope you now know how easy it is to create a bar chart and show the bar chart like this and ensure that it works no matter how Dynamic your pages are thanks for watching as usual give this video a like if you found it useful give it a dislike if you didn't something to do better for next time answer 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

Channel: Solutions Abroad

Views: 21,628

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 bar, power bi bar chart, power bi variance, power bi overlapping bar, power bi variance report, power bi var

Id: 6UQaAlYZc78

Length: 15min 42sec (942 seconds)

Published: Mon Sep 18 2023