Creating a Dual Variance Chart w/ Overlap + Error Bars in Power BI

hey data fans Reed here today we have an exciting new update for the visualizations in powerbi now with the February 2024 release we have an option as you can see in front of us here to do bar overlaps where instead of having the clustered column or bar charts stacked next to each other we can actually add a degree of overlap and control how much they overlap with each other between two different measures now on top of this we can also even incorporate those error bars into some VAR bars as well to really show two different comparative values against the base value that we have so we're going to explore all of these in turn so let's go ahead and hop into powerbi and get [Music] started so for this demonstration I'm going to show you a few different methods for incorporating the overlaps but also how to actually turn the error bar into another variance comparison the first one that we see in front of us here is a final product that is actually showing sales amount here on the top as the blue column the column in the back the gray one is the prior year and then it's comparing to the forecast so these bars show if the forecast is above or below the initial value now I want to build this out one by one but this is what the end result will look like so I'm going to come over to the demo start for variance line and let's walk through how to build this so I have the visual here as it would come out of the box today before we turn on any of these features which right now is essentially just showing you a clustered column chart of sales and prior year now to start this we're going to come into columns and if you go into the section for layout down here there's a new Option called overlap that I want to turn on down here at the bottom and then space between Series so as we increase this so let's go ahead and make this maybe like 85% the bars will start to overlap each other and that's a little bit too close so I might even drop this down to maybe about 75 there we go now as they start to overlap generally speaking my personal design preference is to call out the primary value so that means that that's the only one with the data label that I want to have turned on so I'm going to come to series and I'm going to go to Prior year and one thing I want to do is deemphasize the color just a bit so I'm going to turn this to a lighter gray that helps to separate it and move it into the backer in a little bit but still make it visible and then we also want to go to data labels and go to Prior year show for series turn that one off now the only label that will be showing is going to be specifically for the sales that's in the front now last but not least with this we can also now actually leverage the error bars if we want to to be able to show a comparison so in this case we're going to come down and notice that we have an option for sales or prior year and I want to apply the settings to the sales by field and I'm going to come over to this I'm going to go over to any of the calculations that we have over here and I think I'm going to grab budget for this one and I'm going to stick that in my upper bound and I want to make sure to turn the bar on down here at the bottom we have to turn it on under options and we need to turn it on under bar and what that will do if I only provide it one of these two upper or lower bounds it will simply give me the variance to that so the error bars essentially can be turned into a variance bar this indicates right here if I hover over that that just shows me what the forecast is and what the sales is now un today I can't relabel this so it has to be called upper we will find some ways to account for this and provide some context but as of today at least February 2024 release no way to rename that we can however though under options for the aror bars come to the tool tip and instead of showing the initial value if we wanted to we can show relative percentage so if I hover over this this actually shows me that my sales is 37% below whatever my forecast is so it just shows whether or not it's higher or lower and this means that my sales is 48% above it because my forecast is minus 48% of the value of the Sal so it can still be used as a reference amount now what I ended up doing to create a tool tip for this is I used another visual um essentially to to build this out so in this case I went ahead and added a tree map something like this and I just added my sales my I think I wanted to go to Prior year sales so py put that into there and then my bu so budget also goes into there nothing else other than these three values and what I'm going to do with this actually I'm going to move it up here I'm going to go ahead and turn on the legend there we go and I essentially shrunk this all the way down got rid of the title category labels I'm going to make sure that my header icons are turned off my tool tips are turned off and put a background on there we go and make sure that's 100% um other way around 100% here or 0% to make the background filled in and I'm going to shrink this and then this what we can actually do is we just want to make sure that the colors are aligned so we're going to come to this here go to Legend and colors down here it sales becomes blue prior year sales becomes this gray and then budget is that and then we can stack it right about there and then if you wanted to you could also group the objects together to make them a bit more concise group them and then we get the achieved effect that we would want to with these three but I really like this visual to have two comparative values where you have your primary a secondary comparison and then even a third and then the legend itself speaks to what the colors represent but I love the offsets uh again a big shout out to the powerbi core visuals team with Miguel Myers who's helming all of that uh continually happy to be a participant and being able to help evolve the products but we get so many cool releases like this that come out that let us enhance our native visuals now I don't want to just stop here I have a couple of other variances of this as soon as I saw that my first thought was I would love to have these error bars or the variance lines I want to move them or offset them to either the left or the right as an alignment on this I don't want them necessarily in the dead center so I once again found a way to do this this one's a little bit more of a a tweak to the system I don't want to use the word hack but it is something that uh requires a few more considerations but I I got an achieved effect from it so here's a variance bar uh if I come back over to the line offset here and one more over actually let's go to this tab so for the column chart I was able to basically move that bar over now there's a few hidden techniques to achieve this but it has the added effect that we need to if I hover over we still get the upper and lowers we can get the values um for that so it is working um as far as what we need to accomplish onto this but the I'll walk you through essentially the the technique that that's able to achieve this so we're going to come over to the demo start for variance bar and actually what I'm going to do is I'm going to come to the demo start for variance line and I'm just going to enhance this one to move it over so the trick actually comes down to adding a third value in the Y AIS well so I have a placeholder for error bar this is just simply referencing my sales amount so Watch What Happens and kind of see how I'm able to trick the visual to moving the error line over so to start with I'm going to come to error bars for this Visual and I'm going to turn it off here for sales so my aror bar is off for sales now and now I'm going to come to take this placeholder which is the same amount of sales I'm going to drop it onto there notice that we have a third bar showing up so we need to do a couple things first so I'm going to come to data labels I want this third calculation my error bar placeholder I'm going to turn it off for that and I'm also just going to change the name of this value to just a period I just just wanted to have a short name to be in the legend but it has been moved recolored and the labels have been turned off and now I'm going to go to columns going to go to this one and I'm going to turn the transparency to 100% so the bar is now invisible but because I have a third bar that it's also offset based off those overlaps I can add an error bar to the middle of that and Scoot it over compared to all the other ones so it will look like I actually have it aligned so you'll see this in action we're going to come to bars I'm going to go to that third placeholder that I have turn it on and I'm going to add in here the budget again in the upper bound you can already see it's a little bit offset so let's do a couple of tweaks first so the bar here we're going to make this red I'm going to make the width a little bit more I'm going to change the marker to maybe like a square I don't really like the border so I'll turn that off so we have the aor bar it's it's moved over to the side but now here's what we can do one I want to turn it to relative percentage because I would like to see that percent if I hover over that but let's go ahead close out our error bars here and we're going to come over to columns collapse this and for the all series layout let's go ahead and change the space between Series so notice as I reduce this it starts to move that to the right so I'm going to say like 50 let's see how that does not quite there so we're going to keep going I'm going to say like maybe 25 so it's almost there and now what I can do is space between categories let's drop this to zero okay we're getting really close and now let me tweak the space between series a bit and maybe add like two or three more percentages one more oh one less okay I'd say that's closen up but we've now on that invisible bar attached an error bar which is in the center of that but the overlap offset now makes it look like it's right aligned to the original column so it is the achieved effect a couple of new want notes with this is if I hover over on the left I'm not going to get the error bar amount I do have to hover over the actual invisible bar to get it so again it's not completely perfect but it is close to what I would like to achieve to have that extra variance bar between the background one for the prior year the sales and then that additional one that's on the top and in this case we want to make sure to also come down and edit this to match the colors so I'm going to go now and change budget to Red there are but I do like this effect it's getting really close to some of the types of things that I will see with ibcs type integrated variance bars and a few others but there's some degrees of freedom that you could have to customize to configure this stuff which I think is really cool and I had a lot of fun figuring out how I could upgrade and modify these um I even turned this as well if I come actually to my variance line um integrated over here integrated variance that one yeah so this one has a similar process to the other one that I just showed you but instead for the eror bars what I did is I simply for the bar made it a width of 10 got rid of the marker and now the bar is thick enough to be almost an integrated variance itself which is a type of bullet chart that I really like as well the sad part about this is I have no way that I've found to actually color Cod it to be positive or negative I've tried multiple air bars and few other techniques as of February this year I do not believe it's possible to make them red or green depending on if it's a positive or negative variance but it's one other way to graphically represent this so hopefully this is giving you a few examples of how to leverage this stuff where you have the I'd say straight up outof the box solution to start with which is Center aligned only using three measures for everything truly out of the box in terms of how they're kind of intended to be used and then there are some fun options as well to start modifying it a little bit and kind of uh upgrading the native visuals with a little bit of a layered technique and an invisible bar to add the offset for now and hopefully they add this in the future but one other option for you to incorporate with that um but otherwise I do hope this is something that you can incorporate into years I would love to hear if you have any suggestions on enhancements or improvements or how you might leverage these in the reports that you have as always drop that in the comment section down below check out some of my related content here in the upper left and as always uh don't forget to like comment and subscribe to help my channel continue to grow and otherwise I'll will see you all in my next video

Channel: Havens Consulting

Views: 3,800

Keywords: Power BI, PowerBI, PBI, DAX, Data Modeling, Visualizations, Tips & Tricks, PowerPivot, Power Pivot, Power Platform, Power Query, Power BI for Beginners, Power BI Training, Power BI Desktop, Power BI Best Practices, Power BI Relationships, Power BI Dashboard, Power BI Tutorial, Power BI DAX, Power Query Excel, Power BI Versus Excel, Power Query Tutorial, Power Query Functions, Power Query Parameters, Power Query Editor, Drill Through, Power BI Service, Fabric, Semantic Model

Id: jiJr5Doffyk

Length: 12min 23sec (743 seconds)

Published: Tue Feb 27 2024