STATUS BAR in Power BI I Build your own LINEAR GAUGE

you want to visualize the overall status of a project or sales development then did you know that you can take a table and then do some magic and turn it into a status bar let me show you how it's done [Music] [Applause] welcome to how to power bi my name is boss and if this is the first time for you visiting this channel then make sure to hit that subscribe button if you want to learn everything about power bi now let's have a look how we can build that status bar now it all starts with creating a helper table that contains the status categories that we want to use later for our status bar okay so we can do this by going to enter data and then here we can make two columns one for the status id and another one for the status description let's say we want to have five statuses so one two three four five and then in the second column we can put in the descriptions here we have very bad bad neutral good and very good now this last line i don't need it let's click on delete and let's name this table status bar all right so we have our little helper table with two columns status id status description and in our example we are going to have five different status categories now the next thing that we need to do is build two measures one for the current status and one that's gonna help us to put in a little indicator the first one i'm gonna call status we're going to start off by creating a variable that stores the key metric that will determine what the status will be so for us for this example that is going to be the difference to the forecast and i already created this measure and i'm just going to refer to that measure okay so sales versus forecast percentage difference okay so now we have to determine what the different cutoff points are for our status categories and then take that value and put it in one of these status categories now a perfect function for this is the switch function where we are going to use the true expression as the first argument and then we want to check for different conditions that determine then the status category let's now refer back to the variable that we just created so it differs the forecast when it's below or equal to minus 10 so 0.1 then i want to assign it to the first status category now let's duplicate this line so that we have one line for each category okay so let's select the line and then duplicate it five times and over here we have category one two three four five and then we can change the cutoff points so let's say minus five percent then we have plus five then we have plus ten and bigger than done oh i just missed some zeros over there so let's add those as well and now the measure is fixed then we need to add one more measure now this measure will be the pointer that points to the correct status category okay so let's call it pointer and also here we can use some variables so the first one is going to be the status id and here i can use the selected value function to return the status id when there's only one value there and now we want to compare that with the current status so let's add another variable for that so current status and that is that measure that we just created before so over here there it is and let's put in our return statement now we can use our switch function again so switch and then for the expression we're going to type in true and then we can check for the following condition status id is it equal to the current status and when it is then i want to return a one and otherwise nothing let's close the switch function and that is the second measure so that was actually the difficult part we have our measures we have all of the fields that we need now we can insert table or matrix and turn that one into a status bar so i'm gonna choose the matrix first now usually i go for the matrix if i want to have a horizontal status bar and a normal table when i want to have a vertical status bar okay so what goes where so we want to have a horizontal breakdown for the different status categories so i'm going to take the status id and drag it onto columns okay so that gives me one two three four five then we also want to add something to values we need to have first of all that pointer so that returns a one when the current status falls into that specific category okay so that's the first thing then we also want to show the status id itself so i'm going to take status id put it also over here on values and the last thing that we want to add is a status description so i'm going to add that one as well to the values okay so that looks very messy let's go to formatting and put the values on rows and that already makes a big difference okay so values and then over here we can turn on show on rows and you see that already looks a little bit better then we need to get rid of the totals so subtotals turn it off for the columns okay okay so that already goes into the right direction the next thing that we want to do is to turn that status id row into our colored bar a status bar now one thing i noticed is that we have here one one one one one and that is because here for the status id i'm counting it well let's switch that first to the sum so that we have one two three four five so that we can assign different colors okay then let's go to the formatting tab conditional formatting and then here we can choose the field to which we want to apply the conditional formatting which is going to be the status id role okay now we want to apply a different background color and see that already creates a bar now we can do the same thing for the font color as well so let's turn that on so that we don't see the actual numbers on that line okay now the next thing that we want to do is to turn that one into an arrow okay so i'm going to apply now conditional formatting to the pointer so this is not going to be the background color also not the font so it can go down a little bit more you see here we also have icons now let's go to advanced controls and then here we need to edit the rule a little bit so that we have only one percent we're going to change to a number and then here we want to check if it is equal to 1. so what we are saying here is take that measure that we created before the pointer which only returns a one if the status falls into a certain category okay and then if this is true then we want to return what i can well not this one let's go for downward pointing arrow and let's click okay you see now we already have our arrow and this is kind of this main structure of our status bar now the only thing that we need to do is get rid of most of these raw headers and the color markers and then resize it until it looks good now let's start under field formatting and then here for the pointer i do not want to show it so i'm going to take the font color and turn it into white you see that little one that was there is now gone okay so now let's scroll down a little bit further and here we can say if we want to apply it to the header let's turn it on so that the header is also white now then we can do the same for description so i'm going to field formatting now i choose first status description now for the description we want to have the header and white but not the values okay so make sure that you only apply to the header not the values then we're going to change the font color also to white okay again one step closer next thing that i want to get rid of is that background color that we have for the sum of status id row so this we can change in the values and then you see there is this alternate background color which i want to get rid of i can make it white or i can turn off the banded row style now i do not want to call it sum of status id but sales status or maybe nothing at all well then you can just double click on summer status id and rename it so this is my sales status then we also want to get rid of these grid lines okay so that's also a formatting thing go here to the row headers and then outline we don't want this so let's turn it to none and also for the column headers also there outline let's turn it to none now the column headers themselves we can change the font color for so let's turn the font color to white so that we don't see it okay and that gives me my status bar so we have now sales status we have the bar with the different descriptions in the knee fat and the arrow so now i want to have the sale status a little bit bigger so that's the row header so i'm going to row headers and then turn the deck size up a little bit to maybe 12 and now we can resize the column widths until it looks a bit better so just make sure that you still know where to find the column headers because we changed the font to white we don't really see them that well so just drag it a little bit more to the left and you do that for every category you make it as big as you want it to be okay so now we change the golden webs let's now put the descriptions underneath the bar nicely in the middle okay so let's go to field formatting and then choose status description and then here we want to align everything in the middle or center okay so we want to apply it to the values you see now the values disappeared and that is because before i change the font color okay so what we do is we have to find a different way of hiding that header from before the first status description that we have there okay so i'm going to change the font font color so let's say gray okay so that we have back our descriptions and now we have to find a different way of getting rid of that row header that we have there so what we could do is we could go to row headers and then change the font color here to white but that then gets rid of the sale status row header which is something that i wanted to keep well then we can jump back again to field formatting and then choose cell status and then here on the font choose the font color that you like and then also make sure that you apply it to that let's then also put the arrow nicely in the middle now remember we have there the arrow and a one right next to it but we just applied a white font color to it so you cannot see it however we can also show the arrow only and then nicely align it to the middle however this is something you do under conditional formatting and then let's click on advanced control again and then here we need to choose under icon layout icon i can alignment the middle okay and just double check also here on the field formatting that you put the alignment to center okay now with that our arrow now also shows nicely in the middle okay now let's switch back and forth and let's see if it works so 2019 we had minus 18 you see very bad and then i switched to 2020 very good okay so that's it we have our status bar and we can use it now on a report to show the overall sales development status or we integrate it into a chart and put it maybe in the top right corner just like this and if you don't like the arrow well then you can just go to conditional formatting and then here for the pointer go to advanced controls and then choose a different icon instead so this is where you can get very creative so let's go for star and you see now we have a star instead now what if you want to have it on top of the bar itself instead above it well we can also apply another conditional formatting rule to the sales status okay because that is basically our bar then we turn icons on and then here go to advanced controls then we want to base it on the field that we used before so that's going to be a pointer okay we want to have the icon only in the middle then we can get rid of our rules and change it to the rule that we used before so if it's equal to one then we want to show a icon okay so now we have the indicator on the bar itself and would this work vertically as well yes you can also create a vertical status bar however then do not go for the matrix visual but for the normal table visual instead and then you can put on the values the pointer status id and the status description then change the formatting apply the conditional formatting rules just like before and then you have the status bar in a vertical format if you want more flexibility for the pointer icon or a different icon for each status category then you can do this in your pointer measure then here you can update the conditions that you're checking for so first of all the current status is equal to the status category that's one and is it equal to the first one then a sad icon okay and then for the next one just a nappy until a happy icon okay so different emoji for each status category so now that we have updated our measure we just have to turn off the conditional formatting for the pointer and once that is done you will see that there is now an icon showing right above the status bar with your custom emoji okay so now you know how to build a horizontal status bar a vertical status bar the only thing is that when you hover over it you can still click on it if you don't want it then you can just overlap it with text box group the two objects that's it okay then one more thing that i usually do and that is that i do not want to show the table status bar so just go there and hide it from the report view and that's how we can build the status bar all starting from a simple table or matrix just playing around with the formatting and conditional formatting maybe you have even more ideas of how we can take this to the next level let me know in the comment section below now if you want to play around with this file i will put the download link in the description below and if you haven't subscribed yet then make sure to hit that subscribe button i hope to see you in the next video

Channel: How to Power BI

Views: 39,193

Keywords: status bar, status bar power bi, power bi status bar, status bar in power bi, linear gauge, linear gauge power bi, power bi linear gauge, linear gauge in power bi, custom visual power bi, power bi custom visual, custom visual in power bi, status visual power bi, visual status power bi, power bi project tracking, tracking power bi, power bi tracking, status power bi, power bi, power bi tutorials for beginners, power bi desktop

Id: 2ClAphW9usY

Length: 15min 42sec (942 seconds)

Published: Mon Mar 08 2021