Trend Visualization in Power BI

if you want to compare two periods with different categories well then one option would be a normal column chart that shows the values and the difference from one period to the other but if you really want to emphasize that difference then maybe a better option would be an AR chart that shows an arrow from the previous period to the current period now in this video I'm going to show you how to set it up of course only using native features let's get [Applause] started [Applause] welcome to how to powerbi my name is boss and if this is the very first time for you visiting this channel make sure to hit that subscribe button to stay up to dat on all of my videos in which I share everything I know about powerbi let's get started and see how we can build an a chart which is a visual that you might want to use if you really want to emphasize the difference from one period to the other now as an example we're going to use this chart over here as a starting point which is at the moment a column chart that shows the current sales values for different products now first of all let's take that chart and turn it into a line chart because I don't want to visualize columns I just need our data points which going to serve as our starting points for the hours that will go up and down now here I'm still visualizing the total sales however as a starting point for these arrows up and down I need the sales from the previous period now for that we have over here on the right hand side two other measures first of all we have sales current quarter let me open it up which figures out okay what is the current quarter in the filter context all right so one quarter year needs to be selected and then I use that to put a filter using that calculate function all right now that's the current quarter sales then I also have the previous quarter sales which just well takes the current quarter sales and pushes it one quarter back using also calculate and a date ad function to push the day one quarter to the back all right now that we have these two measures and can go back to our visual go here to the builds panel instead of total sales I'm going to take over here the sales for the previous quarter because those are going to be the data points that serve as a starting points for the ARs up and down all right then I want don't want to show that line so let's go here to the formatting options and then here open up the lines group and I want to set the stroke with to zero because I don't want to see the line that connects the dots and then we can turn the markers on okay now the data labels for the time being we don't need so I'm going to turn those off now these data points I want to show in green if the current quarter sales are above the previous quarter sales and in red if we have it the other way around now you might think oh here in the market we have conditional formatting for the colors but we don't all right so that's disappointing so we need to fing of a workaround now one thing that we could do is to create two new measures one measure that only shows the sales previous quarter when the current quarter sales is above it and one measure that shows the sales for previous quarter when the current quarter sales are below it all right now let's create these two measures so first one this is going to be s previous quarter and then positive and then we can say if and then the current quarter sales so the current quarter sales is above the previous quarter sales only then I want to have the previous quarter sales all right now let's copy that whole measure and then insert another one and then we just have to reverse it so I base it in over here positive will be now negative and over here I don't want to have a bigger than sign but a smaller than sign now what's the point well now we can instead of having here sales previous quarter we take sales previous quarter negative and positive all right now that gives us all of the data points again however now you see we have a different color there for the positive ones and the negative ones because well we don't have that conditional formatting option right so what we can do next is go here back to the marker colors now select the series for the negative ones now there I want to have the color red let's pick it over here and for the other one for the positive ones I want to have the color green okay now and you see now we have kind of conditional formatting without needing that conditional formatting button over there so it's a little workaround okay now these values are going to serve as a starting point what AR is up and down now now to get the arrow that go up and down we need Arrow bars so let's go to the formatting options and then scroll all the way down to the Arab bars group and here we have the two series sales brief squ a positive and negative now let's start with the Positive hours that go up and then open the options enable them and here we can put in an upper bound or lower bound now here we need the upper bound because the previous quarter that's going to serve as a start point and then we want to have hours that go up now where should it stop with the current quarter value for the sales however I don't just want to take over here the total sales that will not work because that will also put in the errors over here where they need to go down all right so I need to have two more measures that will be very similar to these two over here so I take one of them just copy them again then add a new measure let's baste it in here and here we have sales current quarter and then positive if the sales current quarter is bigger than the previous one then I want to have the sales current quarter all right now then again copy this and then also create one for sales current quarter negative so new measure baste it in there then positive becomes negative then over here reverse the sign and that's it now we're ready to go back to our Arrow bars so here we are under the formatting options Arrow bars and now we can put in that upper bound and the upper bound is going to be determined by sales current quarter positive and you see we have the erors that go up now it doesn't look completely right just yet however that is just formatting let's now move on to the bars choose the bar color which is going to be green and then here the mark Mar that we want to have is that Arrow okay now here you can play around with this size if you want it to be bigger or smaller I think it's fine for now and then we can also over here get rid of that border it's a white border so you don't see that well so I just put it to zero all right and now we just have to repeat the same thing for the negative bars so let's switch the series go to options now we don't want to have an upper bound but a lower bound and here we can just use the measure that we already did before sales current quarter negative all right that looks good now we just have to update the formatting again now here we can choose the bar color it's red then set the Border size to zero marker shape is also going to be an arrow okay and that already gives us the arrows that go up and down so the main thing is already there now the next thing that we have to talk about is the labels now one option would be to put the labels in using aror labels all right so let's give this a try so let's turn them on and you see over here we have the labels for the absolute values but even that we could change right so if you go here and open up the arrow labels options there we can first of all let's make it a little bit smaller and then over here the label format we can change to for example relative numeric so that you have the change or the relative percentage change okay and even better you can also show a background and that background color we could well give a matching color maybe play around with the transparency I think it's a little bit annoying that we have two decimal places and cannot change that so I'm just going to change it back to absolute values so that shows the change and for the positive ones I'm going to do exactly the same so let's switch over here to the other series for the positive arrows then go over here to Arrow labels again turn them on and over here I can then also make those a little bit smaller give them also a background and make that background color the same color green and increase the transparency a bit all right now that already starts to look pretty good however let's see if we can still do better now at the moment we are showing here for the red labels to change however for the green ones totally forgot to update the arrow labels so that it also shows the change so relative numeric okay now this is probably only a good idea if you would also show well the ending points the absolute values not the just the change all right now so therefore it's probably a good idea to also turn the Y AIS on all right so that we know where it ended now this would be one option however what we could also consider is to well just leave the absolute values in and then maybe have another label there for the data points with the percent entage change okay so for that we wouldn't need the y-axis so let me go back to the arrow bars and then over here we can go and let's start with the negative errors here I change this back to absolute values so I scroll all the way down and say that this needs to be absolute and then do exactly the same for the other ones as well so over here positive and then change this also to Absolute okay so we see at the moment the values of the current quarter but we don't really have a number for the change now to get that value in what we could do is put labels there at the starting points all right now so that means we need another measure again so I'm going to go over here to my data panel and I have over here already me set up where we take the sales of the current quarter divided by the previous quarter minus one that gives us the growth rate and then here in that format function I can say how it should be format it all right now that's also the measure for that bar chart that you saw at the beginning okay now I'm going to reuse that so I take the visual then we can go to data labels let's turn it on and then for all of them I want the font size to be a little bit smaller so eight is the smallest okay and then we can customize labels for the negative ones and the positive ones now at the moment it shows the absolute values but I want to have the change the percentage change so let's start with the negative ones go over here to values scroll down and there we can go for a custom label add the data and choose our growth percentage measure that we want to show for the label okay perfect there you go now I want them to be in the color red all right if you want to emphasize a little bit further if you think it's a little bit too much then maybe leave them as they were all right and then you could also go for a background okay so turn the background on and then over here here have a matching color red play around with the transparency for example 95% now I think both are maybe a little bit much so let's turn it off again and maybe instead of having these arrows in there let's go back to that measure so over here that sales grow percentage quarter for quarter label and then instead of that Arrow up I want to have just a plus icon and over here just a minus icon all right maybe that looks a little bit cleaner all right that's better not so overwhelming okay now for the positive ones I'm going to do exactly the same take the chart go to the formatting options then over here for data labels we choose the series sales preview quarter positive then custom label we turn on add the data and then over here we take that same measure as before now here another thing that I didn't do before is the positioning maybe if we just make sure that the positive ones are always below the starting points so here on the options set the position to under and for the negative ones we probably always want to have that label above it so position above and there you go here we have our first version of an arrow chart now of course we can fine- tune it a little bit however I think it's pretty good now another thing that I would consider doing is to instead of having that percentage there at the beginning maybe we want to place it next to the line so somewhere in the middle now the problem is how do we get it there because well there is an option to play with the minimum label offset now if I do that let's see what happens well over here it goes up and down okay but still not possible to get it nicely always in the middle and another thing that is kind of problematic is how can we push it a little bit to the right okay now let's see how we can achieve that with a little trick okay now to get it always in the middle we probably just need a series that Returns the data points for well the that are in the middle between the current period sales and the previous period sales so again another measure so let's add over here a new one and let's call this one Sales middle and then over here we just want to have well the sum of the uh previous quarter and the current quarter all right so let's go and look for a measure sales current quarter Plus the sales of the previous quarter let's add that between brackets and then divide it by two now we still need opening bracket there at the beginning all right then I'm going to add the sales medal to a visualization okay now you see we have now these dots always exactly in the middle of our Arrow bars now let's then do the next thing which is the formatting right so we go here to format then the mark markers for that series I don't want to show so I turn Sales middle markers off and then we just need to start playing around with the labels again now the labels for the well the starting points we now want to get rid of right so we have to go here to sales pref squ negative and then turn the labels off okay we do the same thing for the positive one and now we take the middle ones and they're those we want to have in the middle all right now here let's also make sure the values are as small as possible okay that's fine and then we want to we have a custom label so let's turn the custom label on go to add data Matrix and over here take that same label percentage as what we had before okay now it nicely shows more or less in the middle well more or less not exact now to get a little bit more exact we probably have to put that offside a little bit more to zero okay that's a little bit better now if you're still not happy of course you can also play around with that value itself instead of dividing it by two maybe choose a different number like 2.1 or 1.9 and try to adjust where the placement is of the labels okay now now that we have it more or less that in the middle we want to take that percentage and push it a little bit more to the right so I'm going to go back to the measure and here I just just want to add a little bit of badding to the left of that number okay so I create another varable panning left and over here let's try this first so let's say we have five spaces now Banning left I'm going to add as a part over here on the left hand side of that percentage all right and let's see what happens well unfortunately nothing happens so let's go back again and here we need to have a different character that doesn't get removed by powerbi now one of these characters is an empty character that you can get from EMP character.com all right so I'm just going to copy that from here then go back to powerbi paste it in there and this is what you get all right now that we have this let's then do this inside of a repetition function and there we can say how many times do we want to repeat that empty character for example 20 times now let's try this and you see this one does push the percentage to the right so that's much better now the question is just how many times do we need to repeat that character let's try five that is a little bit too little so I have to increase it maybe to 10 and you see it's already a little bit better but not perfect so probably 15 That's The Sweet Spot It depends a little bit on how wide your visualization is all right and now we have our labels to the right hand side of the arrow bars perfect now maybe you want to emphasize it just a little bit more which we could do by making it bolt and so here for the data labels let's make them B okay now another thing that we might want to do is well sort it in a certain order now the thing is though we have now here only sales previous quarter negative previous quarter positive and middle let's say I want to sort by the previous quarter then this is not going to help us because if we go here to sort options you see we only have these two so if you want to have a sort order by all of the values from the previous quarter or the current quarter then you go here and add those to the tool tips so if I go here sales current quarter now you will see that shows up over here in the sort options can take it and sort it in either descending or ascending order now there you go we have everything in place for our Arrow charts now the last thing is just we need to to refine it a bit with the spacing because now at the moment sometimes it just looks a little bit cramped now this would not be the case if I make it a little bit wider that would be an easy fix all right and then we just have to play around with the spacing of the labels Etc but that's it here we have our a charts now I hope that you like it and let's try it out one last time so over here let's choose a different Corner perfect nice okay now let me know your thoughts your comments in the comment section below if you like all of this design stuff and visualization tricks then make sure to check out these two videos over here and if you want to build reports together with me from beginning to end then make sure to have a look at this upcoming training now thank you for watching I see you in the next video

Channel: How to Power BI

Views: 31,774

Keywords: powerbi, power bi, data training, how to power bi, bas dohmen, arrow chart, power bi visualisation, power bi design, dax, error bars, conditional formatting, power bi tutorial, dashboards

Id: Lp9d7vcHr0E

Length: 19min 46sec (1186 seconds)

Published: Wed Nov 15 2023