Hey guys welcome to another video on the channel if you don't know me my name is Jefferson Alves I specialize in Power Bi in today's video I'm going to show you something I had published on Linkedin I think last month and many people have asked me how to do it, so we can put two data labels here in a native Power diy column chart and these labels are independent so I can format one of the labels and another format on the other label OK ? all of this here uses only dates so notice the following in this example I have the evolution of billing over the months and these little bars that are darker represent the months that the goal was reached OK? So look, I have the label with conditional formatting, right, green and then the months that didn't hit the target. Look beyond the bar, it becomes clearer, the label turns red . the green color OK? this here is the scenario that I'm going to explain step by step how we do this here regarding the goal but we have another example here that you can apply referring to the percentage hieroveria That is the percentage change referring to the same period of the year previous so Look, I'm here with the year 2020 selected, the seller is Lázaro So it means that in the middle of these months here, look at the same period of the previous year in 2019, it had a decrease so it was red here already in November, right ? here one where the conditional formatting turned green means that in relation to the same period of the previous year it had an increase of 26% OK? So what am I going to teach you in this class today, everything step by step? You'll already be able to partially apply this here in your day to day, OK? so if you already liked the idea of the personal class, be sure to like and subscribe to the channel and activate the bell OK? because every time I publish a video you will be notified there first hand beauty and also comment a lot ok guys to help here with the engagement YouTube sees that this content is relevant and deliver it to more people so without much ado Let's go there And then guys I'm here with a blank page and we're really going to start building our look here from scratch OK? so let's assume that we are going to use Two Measures a measure that will be responsible here for the size of the columns which in my case will be the billing and an Extra measure that will appear here as the second label which is the percentage of the Goal OK ? in my scenario it's this one But then you can get the tricks that I 'm going to use here, I'm going to teach and adapt it to your reality Alright so let's go right click let's add a visual the visual that I'm going to use It's this one with columns graph of row grouped columns I'm going to resize this look here to get this size and now we're going to bring the information from it, right so the X axis will be our abbreviated month And then the Y axis of the column will be the main measure Look, right, then already we have a graph here and then this graph here look this graph will become This ok so let's start here doing more cleaning these titles here I don't need it so a right button a right button format now, right I select that I don't want to go this title I don't want this other one here too I don't need it OK? now what I want to do I'm going to get here on the Y axis look at the Y axis Y axis and normally its value here comes as automatic the minimum value OK ? so I'm going to reset this value Look, I just put the value of 0 here . to be zeroed for us to have fair comparisons here, especially in this case here, what we are analyzing over time, always want it is always possible to zero this value here on the Y axis OK? I speak whenever possible because in some rare exceptions it is not legal to reset it to 99.99%. this minimum value here, the ideal thing is to get it to zero right now, right? I want to add the data labels so we can come here, this brush, and mark the data labels here, OK? And there we go quickly come with this background thing here is horrible, right, so now on the tab here on the side of the format, I'm going to select the data label here, the series that we're going to format this billing that was the measure that I just inserted the and the background screen, I'm going to deselect it with this has already appeared, you can see that some values are inside the column, other values are outside in our example. I want all the values to stay outside the column OK? so we're going to configure this so we're going to come here options instead of automatic we're going to put it on the outer end Okay, then you'll talk to Gerson but you put the outer end and this value here, it stayed inside the bar but it's because the column it reached the limit here of the Y axis Look and then it can't go up because this part here at the top is nothing for the title So how did it not fit out of the column then Power put it inside this bar OK? but that's not what I want so we need to use data to make our Y axis here dynamic What do you mean Jefferson Look just now here on the Y axis in the range Look at the value here we failed in the hand, right the zero however the maximum here, it's on automatic, we have this FX where we can use a measure, right, for us to make this Y axis dynamic so that all the labels are outside the column, how are we going to do this, think about next one with these filters That I applied here, taking into account all the months and the seller Rodrigo, which was the highest value, the highest value, you can see by this Barrinha here, so the highest value was 14 million comma 4 14 million OK? so this Y axis of ours, we can put a higher value on it here at the most, for example, put 18 million let's see 18 million Look, I'm just going to type it by hand Here it is 18 million 1 2 3 4 5 6 now with 18 million it will be good that the value it fit here outside the column OK ? but this here has to be dynamic this value of 18 million is cool for this filter here Rodrigo is just filtering for example the one let me see here look another year I will select here look 2019 Look it's not cool anymore right because the biggest value was this one, 21 million so for this filter that we just used, the ideal would be for the maximum value here to be, for example, 25 million Look at this 25 1 2 3 4 5 6 there all the values and a KB here for out of the Beauty column so we need to leave this here again OK? I'm going to delete the value here to go back to automatic And then how are we going to make this dynamic OK? so what will be the logic we will capture What is the highest value here in our context OK ? taking into account here every month the highest value is this one here in this case here is 21 million so we need to take this value and repeat it to everyone here, all the bars will have 21 million here OK? This will be the logic Show Beleza Jefferson how are we going to do this a measurement So I'm going to come here in the measurements folder I'm going to create a new one and I'm going to put the name here as the dynamic axis put this there put equal then the function that the people are going to use this one, Maxx this Maxx it will ask for two two arguments the table and the expression the table is which is the column that I have here on the X axis of our visual And then the expression is the measure that we did you use na to make the size of the Bars here, which is billing OK? so let me create some variables here to explain better and what we're going to do so I'm going to create a table here and it's going to be the same So what was the column we used here on our X axis was the abbreviated month OK? so the Max function we saw that its first argument asks for a table not a column so there's no point in getting here try to put the abbreviated month calendar there because it won't because the abbreviated table is a column and it's not a table to transform this column in a table I'm going to use the function when select OK? the function to the selector will ask for a column so now we can call it abbreviated at first I'll put the value one just so I don't lose the measure OK? but I need to think about the following Look at the axis of my visual it is formatted in chronological order so it is starting in January and ending in December OK? and why is this working Look because if I came here in this abbreviated month column I ordered this column by another one look at an abbreviated month it is being ordered by month number OK ? Why did I do that because if I left it sorted by the abbreviated month itself, our visuals using access to the column would be in alphabetical order starting with open and ending in September and to stay in chronological order I took this column abbreviated month and ordered it by the same number is a column that goes from 1 to 12 OK ? January is one February is two March 3 and so on ok Jefferson Why are you saying this because this column that served here to order I need to take it into account here in my table it is not present here, in a visible way, but why Behind the dax, that column we used to sort it is hidden Okay, so we need to take it into account the calendar month Mix OK? even now, yes, we're going to get the highest value. the function Max to Maxx it has the table the table was what we just created here in this variable ok And then it asks for an expression which is the billing which was the measure that we used here in the visual in the graph ok Ready now just get here and return this highest value measure, we'll know what was the highest value in this set of data OK? And then something interesting if I take this measurement Look if I take this measurement and return it here to our graph which was the highest value here o It was this then So it will create another column here o where all the values will be equal let's see if this is true I'm going to take this measure I'm going to drag it in here so Look at the blue measure is this one that is this little blue bar is the one that we just created here the dynamic axis so notice that the largest value is this one And then look at all the blue Bars are the same size OK? this here is dynamic look Jefferson why did you do this look at the following reason now we managed to return this higher value in all contexts we are going to add an extra value to this Bar ó instead of staying in the limit here of higher value it went up a little OK? so let's do the following I'm going to put here another 40%, so I'm going to put here 1.4 OK? so I'm taking this value here then adding 40%. then I put a point four OK? 1.4 I'm going to press enter to finish here ah add no right I put another 1.4 times 1.4 OK ? then I added here but 40%. so look what we have here, this was the greatest value, but now our dynamic yin axis is hitting up here . there will be this extra space to create the perfect labels, I just put this blue bar here on our chart so you can see it visually. But we don't need it here, so with the chart selected, we'll uncheck it. well here, on the Y axis Alright, so let's use the graph here, select the Y axis, now here, at the maximum value instead of automatic, we're going to associate this measure that we just created OK? then the value of the field we come here in measures and then we find this dynamic Y axis now I will click on OK? look, we're going to guarantee that all the values will always be outside the Barra OK? something that would be important for us to create too, look today I know what this 1.4 means . so, thinking about future maintenance in six months, maybe I can know what this is, maybe let's assume this report goes to someone else, who will know why this 1.4 probably not, right? And then I store this value in a variable I put it here ó 1.4 And then I take this variable and return it here ó I'm not getting the highest value and multiplying it here ó by the displacement OK? I did exactly the same thing, but now using variables and then you could even put a comment on what these variables are about . of the Y axis We've already managed to leave it dynamically now what we need to do we need to insert Look at the other label up here and that's what we're going to do exactly now for us to insert this other label we need to create a new measure OK? So now there's going to be something very interesting here because we're going to do it. Why What measure did we use here in the columns was the billing measure OK? however , I'm going to use the billing measure to position the label here in the place I want it, but within that measure, right, instead of bringing the billing value, I'm going to bring the value of the Target percentage OK? It's complicated, right, at first , but it's something very calm, okay, so let's go. First, I'm going to insert this measure here. it will be the same as some variables to facilitate understanding so at first I will bring the value here OK? The value is the measure that we put here, the main one in our visual that the billing measure there, I'm going to put it here, I'm going to bring this value, but look, I'm just going to bring the billing value, but I want to position it a little above OK ? _ now what are we going to do with this measurement this is not ready yet it's just for us to configure it a little bit so you can understand here the logic that I created show so with the graph selected we will bring it here on the axis Y of the line I leave Y of the line this measure that we just created the percentage goals label so drag here the Y axis of the line OK? first, then, several configurations he enabled here, the secondary axis, we don't need that, okay, so we're going to uncheck it . and turn it off again I think secondary disappeared perfect so now Look at this blue line here we don't need it right so let's go get this line here I'm going to reset it here there now the labels here it the data labels were not messed up right so where what we're going to come here, the data label and now the billing data label, we've already configured it with this little gray here. __ ] I didn't like it, I'm going to put it, you selfish look . Okay, now I want to configure the percentage target label . a color here nothing to do I'm going to put an orange color here and I'm going to put a [ __ ] OK ? ok we already have the label here this subtitle here also has nothing to do here for me for now ok so i will also disable the subtitle ok so look what we did here look we already managed to insert a label OK? but it is not positioned correctly, look, we have this distance here from the granddaughter's label, right, the percentage that will be and this one, look at the distance that we have OK? bigger and look , in this case here, the distance is smaller, so we need to standardize . OK ? And then look what happened here the billing value is what this bar is the Bars here so think about the following this bar here 40% of it How big is this here OK? this other bar that is smaller here 40 Oops, let me put a better drawing here of this bar here 40% is more or less that and of this other bar here, 40% is a smaller value So this one is difficult that we did to get 40% . he's taking the size of the Bars in proportion here OK? that's why the bars where the billing value is greater the distance between the labels are greater OK? and we need to leave it in a linear way, so we need to configure it so that all the values are positioned in the correct places, right, that have the same distance, so based on that, it takes the size of the Bar to make the space here proportional Why can we do it Remember that column Our dynamic axis Where if we drag Look if we drag it Oops if we drag it here into our visual in all contexts the bars are the same size So we'll get it a percentage of this measure here on the dynamic axis because in all contexts we are sure that the values are the same, that is, all the Bars are the same size OK? ok, we already have the logic here so let's do the following I'm not going to create it here so this 1.40 here is the displacement right so I'm going to create a variable here with this name it will be the same so put one here because this icon that is appearing here is kind of boring right because I have the graph selected So ready for the measurement, the displacement will be as follows, we will take that measurement of the dynamic Y axis OK? and now I want to get 40 let me see here 40% I'm not going to get about 20% of this value here so let's multiply times 0.2 20%. OK ? and now look at this displacement I'm going to bring it here instead of the times I'm going to put more OK? Okay, let's see now how the displacements turned out, now all the displacements here are huge, so I think that 20% was a lot . I'm going to reduce 10%, it's a lot I'm going to put 8 so 0.08 cool here let me see how interesting it is here, you see now I have all the labels here, positioned correctly and this here is responsive according to the filter that we apply on the panel Look how cool it is, but now you can ask yourself Jeferson But you're going crazy, you took the measure, right, look at that target percentage label what it is It is the revenue but a percentage of the axis dynamic there is bringing this value here OK? OK, that's it at first , but as of April 2023, we can separate the values, for example, this value that we calculated here on the Target Percentage label was just to position the axis, okay, it was just to position the label where we want it but the value that I want to appear will be another OK? so this measure here will have its value, which is what we calculated, but what we will show on the graph is another value OK? so the Power separated it for us, it separated the value for formation and this from April 2023 so, thinking about the logical reasoning for you to do what I'm going to show you now desktop must contain the update from April 2023 onwards otherwise you won't be able to use it ah Jefferson I don't know how to configure Power desktop everything else So watch this video here where I explained how to do it step by step Alright so assuming you already know how to do this let's use dynamic formatting for us get to this final result And then we're really going to start, right Configure the percentage label here So this selected measure here where the format is, we're going to change it to dynamic OK? and now notice that these two quotation marks have already appeared here in the Formula One bar so we can put any value here that it will already modify in the graph for us look at the beginning I'm going to put my name Jeffsheet This is the value here And then pay attention to the following right here, so you can see the measurement. You come down to this little arrow and see the measurement. Look, OK? ay, the measurement value is still the same OK? we are taking the billing and adding 7% of the value of the Y axis to it OK? The value is the same but what will appear in the graph is something else look at the little arrow down format is what we define here that will appear in the graph OK? so at first what we want to appear we want the percentage of the Goal to appear OK ? the granddaughter's percentage is she a number OK? and to work with numbers here in this dynamic format, it's no use for me to get here and delete and put the yes OK? It won't work for us working with numbers, we have a trick, right, which is to put four quotation marks one two three four and katernar with number OK? then commercial and then again Four quotes 1 2 3 4 OK ? now we already have a number format here, but I put 5 steels here, it's only four, okay, I'm going to press enter. but that's not what I want to do ok and advancing here first I'm going to create a variable I'm going to call it see percentage OK? And then in this variable I'm going to store what is the value that I want to appear here on the chart so it's the percentage measure of the Goal OK? if I get here and put this measure percentage of the Goal down here, of course I need to put a returner Look at the value, it will be in the format of this bad, but I want it in the format, right, of percentage So let's configure this here we will create another variable and it will serve as a percentage I will put it here the formatted one will be the same So we are going to use the format function and then the format function it has two mandatory arguments What is the value that I want to format And then what is the format that I I want it to appear So let's go the value that I want to format the percentage right This variable from above so I'm going to paste the format that I want to appear I'm going to put it in quotes I'm going to close with appearances here from Ford And I want it to appear in the format percentage . _ zero is one decimal place if you want two decimal places zero point zero and so on OK ? but I just want it, right? I don't want houses like that, but I'm going to put zero OK? And then I put the percentage symbol show now let's do the following the percentage of the Goal, it works as follows when the percentage of the Goal It is greater than or equal to 100%, it means that in the months where there is greater than greater than or equal to 100% it hit the target below 100% it didn't hit the target and then I want to use the little arrows OK? like I did here, when I hit the up arrow target, when I didn't hit the down arrow target, so first I'll go here to the site And then I'll leave this site here in the description OK? because on this site we're going to get little arrows so I'm going to select this option arrow symbols the option I like the most here, look, it's already here, option 30, so I'm going to click on it and there's this little arrow here, look, it has this little curve here below which I think is very elegant so the little arrow up I'm going to copy Look just hover over the icon I want and copy it back to Power And then at first I'm going to paste it here I ended up taking advantage of it and I'm going to take the little arrow down also look at the little arrow down I'm going to copy it I'm going to paste it in here too OK? at first I'm going to press enter Look, it's just going to be very messy, right because it's bringing the little arrow straight down in all contexts and the percentage value And now I want to separate it, when it is I have to create a logic here of the following way, when the percentage of the Goal is greater than or equal to 100%, then it will bring the little arrow up, when it is less than 100%, it will bring the little arrow down. is it greater than or equal to OK? It's 100% so we're going to make the format where it will appear here, let me copy this little arrow down and the percentage will appear with the little arrow up . recap here what we did So let's imagine here that the target was 85%, OK? that actually behind it is like this, 0.85 so we stored this value here in the percentage OK? AND then we are doing an IFF here making a check if the percentage is greater than or equal to one or greater than or equal to 100% OK? when it is greater than or equal to 100%, it will appear in percentage format with the up arrow otherwise the down arrow will appear in percentage format OK? I'm going to press enter let's see this one here on our graph it already worked so we saw that it worked, right, look at values greater than 100% up arrow, lower values, down arrow OK? Now we need to do the constitutional formatting, OK? so let's go there first I'm going to create a measure new measure I'm going to put the name of this measure so the target conditional formatting will be the same then we're going to do this, it's practically not the same as what we did now, so if the percentage of the Goal is greater than or equal to one that is greater than or equal to 100%. then I want the green color I'll write it in English then I'll change it to a hexadecimal color okay if not it's going to be the red color OK? I'm going to press enter, I'm done with this measure, now I'm going to select the graph, right? And then I'm going to click on this option to open the formations here. And now what do I want to format the data label ? the axis that I want to format, that is, the series, is this one here, the goals label, be careful, it's yours to be like this, everyone . then we go to the option here, the values instead of bringing this orange color to everyone, we will click here in the FX and we will call that measure, you know, we just made the value of the Field and the shitty conditional formatting and I will click in OK ? so look where the target was greater than 100%. the green color where it was below 100% red color and also the arrows here they are correct now let's go there let's go I'm going to put a color here but nice even I'm going to paste it here but of course let me remove the selection here from the graph to not getting in the way, go back here to the measurement instead of returning these colors I'm going to return a hexadecimal where the red will be a little more discreet and the green will be pulled here more to a lime green Look, I'm just going to press enter and look at this way and now guys everything is dynamic Look as I make the filters on the panel look here perfect now right for us to be something more minimalist you see here look I want to leave where the percentage reached the goal I want the bar to be darker and where the target was not reached, it is a little clearer, so with the graph selected, let's go here with more options . it's a rule based on that field based on the measure percentage of the Goal percentage of the goal and then we're going to create two rules, right so let's change here look at where the percentage is I'm going to put a number So let's go there If it's greater than or equal to minimum here and less than one that is less than 100%, so I'm going to put this gray color here now if it is greater than or equal to one that is greater than or equal to 100%. and less than a value here, the maximum, right, the infinite value, we're going to bring a color here, the darker gray, I'm finding this gray here too dark . to be more discreet click OK ? Look where the target hit, we have the bar here with a darker gray where the target did not hit, a lighter gray okay and then the axis was killed with the label axis, right from the percentage in the target was conditionally killed guys and there now, right? It's just a perfume shop, fix the title subtitle here OK? but that's it, I'll leave it to you okay, focus here main really was on these labels And then guys, this class was this I hope you enjoyed the video, okay I'm absolutely sure that the content I 've been sharing here on YouTube for free you won't You won't even find paid courses OK? This is absolutely certain, so I ask you to like the video and share it on your social networks, help the channel grow. the channel grows with the intention of posting two to three videos to help you I also ask that you comment a lot on the videos here on the channel because it helps to engage, it's the channel and with that YouTube ends up distributing it to more people automatically in addition to the channel growing, other professionals will be helped, right, with the techniques that I've been sharing here on YouTube and then, don't forget, of course, to subscribe to the channel and activate the Tinker bell, personal beauty And then what am I going to do here if you liked this one dynamic formats technique I already have a playlist here on the channel with several videos using this dynamic formats technique so be sure to watch this playlist because I'm sure it will add a lot of knowledge there so that you can develop fantastic skills there personal beauty that's it closed
