Visualizing Status Tracking Data in Power BI

if you ever ordered something online then you probably know visuals like this one over here which shows in what stage your package is in the delivery process now it's a very simple visual easy to understand and can be adapted to other processes as well for example maybe you want to visualize in what stage somebody is in the recruitment process or in what stage a potential new client is in the sales funnel now in this video I'm going to show you how to set it [Applause] up [Music] [Applause] [Music] [Applause] welcome to how to powerbi my name is boss and if this is the very first time for you visiting this channel to make sure to hit that subscribe button to stay up to date on all of my videos in which I share everything I know about powerbi all right now let's have a look how we can build a live tracker now I have over here already a simple setup in which we can choose a shipment and then we can see from where to where that shipment is and then over here some key information about that shipment and what I want to have over here looks like this all right and that's what we're going to build now let's start with step one now step one is that we need to build a disconnected table that contains all of the different stages or statuses in your process now over here we are checking shipments from the moment that item was ordered until it was delivered now let's have a look at an example table I'm going to First have a look at the table view now over here you see that table and this table I built just over here with a simple text formula now you could also have done it of course in power query now you see to keep it simple we only have four different statuses ordered sent in delivery and delivered okay that's it and we also have an extra column with the Sorting order now let's switch now to the modeling View and here you see that the status table has no relationship to the shipment data okay now I'm going to go down to the report View and let's start building that main visual the live tracker now it all starts with just a normal native visual which is a line chart so over here at the top choose line chart and I'm going to put it where I more or less want to have it so over here and let's align it with the cards below it okay now the next thing that we're going to do is from that status table I would like to have the shipment status on the x-axis so let's drag it on the x-axis there you go okay and then we can add a new measure now this first measure is going to be a dummy measure which will create just a horizontal line okay so let me zoom in a little bit and expand this and I'm going to call this one dummy equals one all right now why I want well it doesn't matter it just gives us a base point for this visualization all right now now that I have this measure we can add it to the visual and you see we have a straight Flat Line okay now all of the other elements I'm going to get rid of now let's get started by turning off the title the x-axis the Y AIS the secondary y- axis and then also the titles so the title on the x-axis can go the title on the y axis can also go all right right now over here for the x-axis though let's bring it back for a second I do want to show the different statuses all right so that's better now the next thing that I want to have is a little marker there for each one of the four stages so let's go here to markers turn them on and let's make them also a little bit bigger so let's go down and put the mark a size to 20 which is the biggest size that there is if I go for 21 you see it stops me all right so that's the biggest size so now well actually we are already getting close now let's just update the color all right so I'm going to go here to lines colors and then instead of having here pink or purple I'm going to go here for light green all right so now that we adjusted the formatting let's also make sure that our stages or statuses show in the right order because the right order would be well first of all we something then it gets sent then it's in delivery and then it's delivered okay now two ways in which we can achieve that option number one is to go to the shipment status column and then set the sort by column to the order column the sort order column that you add to that little table all right so that's option one however I have set this table up using over here a calculated table and therefore there's a circular dependency so it will not work this would only work if you for example have used power crey to create a little table so I need to go for a second option now a second way in which we can change the Sorting order would be to take that order column and then add it to the tool tip section so let's take the line chart go and open the builds panel take that order column and drag it onto tool tips because now I can go here to the three dots sort access I want to sort it by the sum of order or the order number basically and I would like to have it in ascending order now you see now we have ordered then send then in delivery and then delivered okay that makes more sense all right so now it's time for step two which is to fill up that line until that point of where that shipment is in the delivery process now for that first shipment over here let's go to the data View and here you see we have that shipment which has the shipment status sent all right so that means it should fill up until here sent now how can we do that well we can create a second measure that basically figures out where it is in the delivery process so I'm going to go here to my measure table new measure now let's call this one Dummy Line highlight and here we can best work with some variables not the first one there we want to have the status of the selected shipment all right so we can say selected and then and Status okay now you can use a selected value function which only returns a value when there's only one shipment selected so selected value is what I want to go for and I want to have the status watch out from the main table the shipment data table status col all right now then I want to have not only the status but the status number now that we don't have it in that main table so I need to get it from a little status table that I created it with a DEX formula or maybe with power query if you find it easier all right so I'm going to have over here selected status number which is equal to now here we can use a calculate function and I want to find the max we can use max over here because we're going to have only one line and then we want to have status order which is the number that sort order number 1 2 3 4 and we want to have it where the shipment status is equal to while what is stored over there all right so over here Des selected stat okay now then we can close that calculate function let me indent this let me put this on the next line and then we can have one more variable for the result now here I want to have a one where the status number is below the sort order number the selected status number of the shipment that we selected okay so I'm going to say if and then here selected value and then order if this is lower than or equal to the selected status number then I want to have a one otherwise a zero okay and then that is then what we can return okay there you go now let me indent this as well and let's try it out all right so I'm going to add it to our visualization so let's select it and over here we have Dummy Line highlight and I'm going to drag it on top of it now that doesn't look exactly what I was hoping for so let's go back for a second all right and then over here instead of zero I just want to return blank okay so you can leave that second argument as this and over here one is fine however if you choose a different value for the dummy so different than one then I would just refer you to that well dummy measure from before which is also one okay now let's see if that changes things and that looks much better all right so that shipment that selected over here is in the delivery process now let's go to the one that I highlighted before which should be in the scent stage okay now it's working okay perfect now the next thing is that we need to clean it up a little bit further for example that Legend there we don't need so let's go over here and turn the legend off now what I also would like is to have little check marks there for the ones that are already checked off right so to get that we can make use of the data label so I'm going to go here to data labels let's turn them on and let's see what that gives us so over here we have one above and one below which is well the label for both the series and you see for stage three stage four these two we only have that first Dummy Line which returns a label the second one well has a black okay so how can I just return it on top of it well let me go here to series Dummy Line Line highlight I'm going to turn off then I'm going to go to that dummy series okay and now I want to put it on top so position above under middle middle is not there annoying okay so we need to think of a workr that lets us take those labels and put them there on top now what we could do is add a third line which goes right below it all right and then we just say for that line we put the labels on top right so above it and then those will overlap with what is already there okay now let's try this out so step for let's get the check marks in all right now I'm going to go over here to Matrix and first of all I want to have a measure that returns a check mark so let's make this a little bit bigger and this is going to be my check mark label all right and then in between quation marks there we need a check mark So Windows key period Then here we go to symbols and then we just have to go down the list to look for it now for me it's a reason reason one so I just choose it from here and close it all right so the check mark measure is there now that measure that's going to also serve as a dummy which will show right below what we have all right so I'm going to add another measure and let's call this one also dummy and then check mark position all right and that check mark position well should be a little bit lower than one because I want to have it below that main line all right so what we could do is we could for example type in 0.9 or we could type in here 0.9 times whatever the dummy value is which is in our case also one okay so this is going to be added now to that main visual so I'm going to take the dummy for the check mark positions and I'm going to add it also to our visualization over there now it looks a little bit messed up however we now just have to clean it up a little bit now first of all let's make it invisible so I'm going to go here to the line and I'm going to set the stroke withd to zero then I'm going to go here to markers select the series again check mark position and here we want to have the markers off okay so now only the labels are left okay so the next thing is to go then to data labels now here we want to have no data labels for the dummy so I'm going to turn that off and I only want to have labels for the check mark position dummy so here we want to have data labels on and then here I want to have for the values a custom label now that's going to be a check mark measure so I'm going to go to my measure table choose the measure check mark label and now we have check marks everywhere the next thing is that we need to push it up all right now here there's still a y- axis that does some outer scaling so I'm going to go here to that y axis and put the minimum on a auto so that was fine and the maximum let's play around with it let's put it at three all right looks quite okay but probably we still need to adjust the data labels a little bit for example here this minimum label offset if I put that to zero you can you see we position them now little a little bit more in the middle all right so over here just play around with this minimal minimum label offset or you can also go here to the um dummy check mark position and then for example make this a little bit higher so that you have it exactly in the middle of the circles okay now then we probably also want these check marks to be in white all right so I'm going to go over here to values then for the color I'm going to choose white okay and some at some point along the way I also got rid of the main line so I'm going to go here back to lines and then here dummy and for the dummy I do want to have the line so I'm going to put this one here to three and for the Dummy Line highlight I also want to have three all right now the hard part for our visual is done in step one we created a disconnected table with all of the different stages and a sort order number for each stage now that makees sure that all of the stages in this visualization are always visible now in step two we created then the line chart we added a w measure which then visualized a horizontal line and we added markers to visualize each different stage as a circle now in step three we then added a highlight measure that highlights the line up to the relevant point for the selected shipment and then in that last step we then added the check marks now for the positioning of the check marks we needed again a new measure to well push the check marks up to the circle level okay and that's where we are now we just need to clean it up a little bit for example I would make this maybe a little bit less high so I'm going to drag this up then I also want to have extra padding so I go over here to visual size and style and then here we have padding and let's put everything to zero however I want to push it up a little bit so do 20 now then the colors well here I already went for well more bluish theme right so I'm going to go here to the line colors now here we can choose the relevant Series so here we can switch to all then go to Dummy Line highlight and I want to have blue so let's go to more colors choose the blue color that you like all right that's almost the same all right perfect now then we can also do a little bit of rounding for this line chart overall so I'm going to go to size and style visual border let's turn it on color I'm going to make white and then rounded Corners let's put it up a little bit all right and then over here the access labels let's make them bolt so I'm going to go here to x-axis and here I want to have the x-axis in Bolt maybe a little bit smaller just like this now on purpose I left a little bit of extra space above the line which I can now use to well display other kpis about this shipment for example the progress as a percentage all right or maybe the number of estimated days until delivery okay now for that I already have two measures so for example here I added a progress measure let me open it up you see over here I just figur out the selected shipment status and then on that basis assign a certain percentage now of course in reality this logic could be way more complex however here I just kept it simple 25% for the first stage then 50 75 and 100 okay now let me try to add it over here which we could do with a normal text box right so if we go here to insert text box and here I want to have something like shipping progress and then colon and then I want to have well 50% which will be returned by a measure so value and then I want to have the progress measure so I look here for my progress measure and it already gives you what would be returned at the moment 50% perfect and name your value so over here this is going to be my progress so here we have our progress value okay save and that's it now I would like to make that value that we have here bold so let's select it and then contrl B to make it bold click there all right and then we can make it a little bit smaller and and maybe you don't need the background so I'm going to turn that off and then we can just drag and drop it to whever we like okay now we just have to do it once more but then also for the estimated delivery days so over here again just another measure that Returns the ETA in days okay now I'm going to add another text box so text box over there and here we're going to have the estimated delivery time and then a number and then days okay now that number is going to Beed by that measure so over here instead of a DOT I'm going to click your value and here I want to have the estimated delivery time there it is already and also here let's rename it so estimated delivery time okay click on Save and again let's make the number B now then also here turn the background off make it fit exactly that text box all right and then we can just drag and drop it wherever we like it to be and of course always make sure that it's nicely aligned meaning that over here it's nicely aligned with that circle on the right hand side so I have to push it a little bit further to the left all right so that text box is more or less aligned there with that Circle below it on the right hand side and the same here for the shipping progress It's nicely lined on the left left now we just have to make sure that that text box and the first one are also aligned from the top so I'm going to go here to view and then open the selection pane which we can just also do now with these buttons on the right then here these two text box boxes were added last so I'm going to select them format align top all right that's it this is our live tracking visualization which is basically just a line chart with a few tricks applied to it to make it look like this now let's see it in the action once more I'm going to to click over here on the drop down choose a different shipment and Tada beautiful now let me know your questions and thoughts in the comment section below I want to thank you for watching also make sure to check out these videos over here and see you in the next one

Channel: How to Power BI

Views: 47,120

Keywords:

Id: nxvapCD2DoU

Length: 20min 44sec (1244 seconds)

Published: Wed Nov 01 2023