You the high level numbers at the top here we're seeing the open orders we're seeing the average order age in days but we're also seeing a really beautiful distribution by their age in days and if you resume in that you can see that yep you know kind of zero more than 50 more than 100 days and so forth so you see that beautiful distribution and of course once you have that you can do with power bi when only power bi can do which almost looks like magic to me still which is you can dive.
Deeper into it so you can say hey show me what's going on with these ones which have been open for a long time so i'm holding down the control click to select that and then maybe i'm sitting down with the person in charge of the germany division uh maybe the sales of the manufacturing unit and i can just hold down control and click germany again and now i know exactly which issues i need to talk to them about right so we can say hey let's focus on serving carrie sabrina larry.
They have orders from germany which are open for a long time again just an example but you can see what's possible with power bi with a relatively simple report and frankly that's what i love about power bi it's a simple stuff yeah there's a complex stuff but you know it scares me just like probably scares you but i love that so much is possible with something really simple so let's see how we can do this now if you are a power bi beginner or still feel like a beginner then.
This channel is for you my friend so make sure to subscribe and click that bell so you are notified whenever i answer your power bi questions now this question comes from our learn power bi member paolo damiani and paolo and i discussed this issue where he was working on for his client and if you are interested in asking questions well all you have to do is go to learnpowerbi.com question so let's start building the solution but before that let's start where i always like to.
By the way if you have no clue what i'm talking about data table lookup table then check out my power bi tutorial which now has i think more than four million views big thank you my friends all right so let's uh so now that we have seen the data uh let's look at the work order data itself now this is shown in power bi just to make it easier for me to explain it to you i put it in excel as well just as dummy data so imagine we have the work order number which uniquely.
Identifies a work order or a specific item and then we have some kind of a start date and then we have complete date now of course we're focused on these items which have the complete date as blank and think about it then how are you going to calculate the age of these items now we could do is i could say oh well i'm just going to take today's date and minus the start date and that'll give me the age but of course you notice that this isn't quite right and you can.
See that these items are actually not open so they should not be counted so of course we're going to have to do something a little more sophisticated and we're going to do so we're going to say if this is blank and i think that's how you do blank in excel i feel more comfortable in power bi now than that right so if it is blank then it is open then let's calculate the age otherwise just leave it right so again.
Sometimes i do this in excel and i call this the human learning i always say that human learning comes before machine learning so before i can teach power bi something i gotta learn it myself and i often break it down and step by step now the odd thing is that that's what you need to learn and master power bi people think that oh i'm not smart enough actually i think at least that's what works for me i need to dump it down i need to break it down like small simple steps.
Because that's how power bi works as well so yeah you need to dump yourself down all right i'm getting carried away so let's come back here and of course we're now where are we going to do this in is in the query editor now of course some of you may be thinking or asking yourself right now hey shouldn't we create a measure for this you probably should but this is the easier approach and since this video is at least focused on the beginners i'm going to show that to you at the end.
Posts Related:
We can talk about the measure approach in a bit all right so here we are in the work order table
And we have the order start date and order complete date so to add a column we're just going to go to add column over here and then i'm just going to click custom column all right there we go let me zoom in so for custom column uh i'm going to call it age and here so i need today's date which is date dot from date time dot local now.And that is today's date and of course i can do minus uh hold on guys okay and i'm gonna find that column and i just double click on that oh it was right there so minus start date but again remember we had a test in excel we had to check that you know we wanted the completed date to be blank so i'm going to add the same check here so let's go back in again and i'm going to say if oops if order complete date is null not mall null then.
Uh then all right then we're returning this answer otherwise we're just going to return the blank all right looks good no errors i'm going to hit ok and yup uh well we'll know in a second i see a whole bunch of nouns but i'm pretty sure the data is there so i'm going to close and apply and i'll be right back and by the way if you are following along then make sure you have the file from learnpowerbi.com.
Download all right so now we have in the fields the work order age so let's put that on here and i usually like looking at things oh did that work no okay let me try that again drag and drop and i often like looking at things in a table great sometimes by default you drag something and it shows a different measure and i already know that there is a problem uh because i did this when i was practicing this as well which is you notice that the sorting is wrong.
And i forgot to change this to a number so let's go back to our query editor and i'm going to change this to a number so work order go back and age i can see from this icon over here actually a lot of other fields need to be changed but we can ignore those we're focused on this so we're we're going to switch this to a whole number age looks good now but of course we're looking for the the count of items by age so i'm going to add the order count by the way that's a really simple.
Formula count rows and now here's the thing here now we can see the order count by age but there are two things going on for one we probably um you know so so the ones which have been closed are showing up in this bucket now we can leave it as that there are lots of ways to filter we can say that age is not blank we can say that order complete date has to be blank but since we're doing this in the query editor i'm just going to go back and add a cleaner column.
Which will be the work status so work order and i'll just go back here add column custom column and i'll say work order status and it's going to be really simple similar to the other one and it'll say if the order complete date is null then it is open else it's um else it's closed does that look good oh the equal sign okay that looks good work order status open and closed and let's try again.
So now i have a little bit of a cleaner column that i can use work order status and i can add this to the page or i can add this to a specific filter in this case i'm just going to add it to the page and i'm going to say i only want to see the open items and now that one is filtered out now of course we can visualize it just like that so again when i'm working with measures or things like.
That i mean this one is pretty simple but i still like to work in table or matrix and only then do i think about visualizing it so in this case let's just try a column chart so you can see that we're not quite there yet i mean yes it is showing order count by age but that's not how we want to see it right we want to bin it or group these so we can have a cleaner view now that's thankfully really easy to do in power bi.
For that we're going to go to the age column that we just added click on the three dots here and
We're going to create a new group that brings up this dialog box and in this case we're just going to leave it to default bin you can explore the other options if you like and i'm going to hit ok and that is going to give you another column here so let me leave this one as is i'm going to make a copy and the second graph we're gonna uh use the bins and you're gonna see the difference so.Instead of age i'm gonna drag and drop the age bins over here and you can see how this looks a whole lot cleaner right so you have uh you know kind of these different bins is it one zero fifty hundred one fifty i mean those are the labels but uh you know the bins have uh let's see if you hover over that it's uh yeah it's kind of an odd number but but still you you kind of get an idea of the age let's talk about what are the possible next steps you could do from here all right so i.
Cleaned up the view a little bit added some basic measures and again if you grab the file you'll be able to examine some of these other measures and one thing i realize is that the age i can actually go and oops and edit that group and the bin size it does try to calculate it automatically based on the different values but i realized that it's it's better to not have the bin size as this weird number 19 so i'm going to i'm going to just change that to 20 hit okay and that'll just give.
Us cleaner boundaries but again we'll talk about the next steps and i'll show you what else may be possible here so this gives you the same view that i showed earlier with all the awesomeness that you can do you can kind of click filter and so forth actually let me fix one more thing all right so uh you notice that the filtering was a little different than if you go back to the beginning of the video and that is controlled by edit interactions so i'm not going to go into that but.
Notice that now the filtering works differently it doesn't highlight it actually filters this one so that's great so as we did earlier you can kind of click and select and focus in on just the germany items and again grab the file now let's talk about what else is possible i'm not going to be able to cover this in this video however that is our plan for actually our advanced power bi training program that we will be announcing soon you can go to learnpowerbi.com.
Advanced if you're interested in that and find out more but let's see some of the ideas that i had of what else could be done i i love this data set and i think it's super interesting the first idea that i had was to again these bins are kind of awkward and i really don't have when i build it for my client it's usually more specific and i would probably do end up doing something like this and notice that you know there are cleaner labels 0 to 10 10 to 30.
And so forth and notice something interesting here that these bin gaps are not the same and you know sometimes clients do want to see this way so i want to have more flexibility this one is 10 days this one is 20 days when it's 30 30 and of course i love doing this part where you don't want to show a long tail like there's you notice how in this in this graph there's this one guy sitting out here and imagine there were a few of these and it's always hard to kind of select them.
Or something right so i just want to say you know what 90 plus and then they could click on it and they can see the details and and so forth right so kind of these customized bins i love doing that but then of course uh god i don't remember now if i mentioned but i i think i did where i talked about the approach that i just showed you used columns in query editor right another approach you can do is using measures and it would be more powerful but it is slightly uh more nuanced.
But with that you can show a trend so you can show kind of how many orders were open at any given time over time and you can see that something bad happened with this company right i mean around may 2021 right and and not just that maybe you can show the the trend of the average age and you can see kind of that spiked up as well but this functionality it's you can't do that with the approach that we showed in this video but it's definitely possible in power bi well i hope.