The Only Power BI Portfolio Project You Need

all right so today you're going to get a powerbi master class in the form of an endtoend powerbi portfolio project for those of you who don't know me my name is moan and I work as a data and analytics analyst within the financial services industry this is my sixth year working in banking and I have over a decade of experience now working with complex Big Data today's episode is special or today's video is special because it's not going to be me who's going to give you all of the knowledge it's going to be my wife actually who's a senior analyst in the mettech industry she is so good working with powerbi she has a couple years of experience now working with the tool she uses it on a daily basis so I'm telling you now pay attention because this is going to be a great project to put on your resume add to your portfolio and increase your professional brand and value but rather than me going on about the project how about I just hand you over to my wife who's going to give you a Master Class in the form of an endtoend powerbi portfolio project hi everyone welcome to the first of the powerbi portfolio projects I will be taking over from Mo today and what we're going to be focusing on is building a condensed Dynamic performance report that really utilizes switch measures specifically and also bringing in some conditional formatting so we can really understand any pain points that this madeup company that we're going to be analyzing has or any potential growth areas I have put together a project structure to really break down how we're going to build this report number one we're going to be focusing on the date so this is bringing in the date from Excel into Power query making any small changes that we may need to do and also building any virtual tables that need to be made for the model in the future the second is the model so this is building any Dax measures or any calculated columns that may be needed the third step is the visual so this is building the lay out and also the Design Within the visuals bringing in the decks and doing any conditional formatting that is needed and then finally we're going to review everything and publish and see how the report works as an end user I think that covers everything that needs to be in the introduction so let's jump over to Excel and just go through our data set for today so the data set that we're using is just an Excel file compromise the three different tables on individual tabs the first of the tables is our fact table it's sales invoiced and the columns that we have to use are a product ID so we can link up to our product table our sales quantity price cost of goods the date of the invoice and also our account ID that leads us into our account table that has all the information for each specific account so these are all our unique accounts that are included in our sales we have our country code our account Master ID account ID latitude longitude country postal code street name and Street number the unique identifier within this is our account ID so we're going to be using that going forward in our plant hierarchy because this is a madeup plant company we have a number of different levels to how we can view our products we have our product family our product group our product name our product size and our product type so lots of different levels to this hierarchy that we can drill down or up into I think that covers everything so I'm going to jump straight into powerbi now so opening up a new powerbi report as you can see I've got nothing loaded yet all I'm going to be doing for this and let me close down my Excel just in case is go to the Excel workbook button click my file and let's open it up so as you can see we have a number of tables that we can bring in the ones we've just covered accounts plant fact and pl hierarchy I'm just going to go and press transform data now there isn't a lot that I want to do in power query notably just amend some of the column titles check the values on what type they've been put at make sure that's all correct and then rename the tables themselves so all we're doing is cleaning it up not making any amendments and then we'll go and start building our virtual tables so for the plan hierarchy I am going to change it to D product I think this is a lot easier to understand what the table is referring to it's our Dimension table and it's got our products in I don't need to amend anything in terms of the titles I mean the headers sorry um I'm pretty happy with that I do tend to in my unique identifier just to cover myself is to remove any duplicates so I am going to click on product name which is my unique identif identification column and just press remove duplicates I'm going to go to my accounts table now which is my other dimension table and I'm going to name it dim account now let me find my unique identifier I'm going to do the exact same thing right click and remove duplicates is there any names that I want to change so I see latitude 2 I am going to clean that up and also Country 2 other than that I'm pretty happy so for plant fact I'm going to rename to fact [Music] sales let me see if there's anything that needs to be changed one thing I wanted to focus on was the date time and make sure that's in the right type which it is so actually let's just go for date I'm pretty happy with that so let's close and apply let those changes be applied great now we can see our tables in the data tab here so one thing I do want to bring in because I know what kind of measures we're going to be building in the future is I do need uh a date table so I'm going to go to modeling new table I'm going to name it dim date calendar calendar tab start date so I'm going to put date Tab and my data cover is from 2022 to 2024 April so I'm going to go from 20 2022 01 for January the 1st of January as my start date and my end date is date tab year 2024 and I'm going to do the full year so month uh 12th 31st I know you may be thinking Nicole you can format that's not clean Dax and you would be right so I am going to show you a trick do not do this for your company's data if you work with powerbi at work but for your projects chat GPT if you go clean up this tax measure let's see if it can clean it up there you go so for your projects if you really want to impress on a technical aspect you can put your uh measures into um CH PT so enter and let's just check yeah I'm pretty happy with that we should have a date hierarchy which I will be using that's perfect effect okay so we have that we will need a column that highlights months that in the past we'll need this when we're looking at prior year-to date and year-to dat measures because otherwise if you don't have this flag within your date table and you're looking at your year to date it's going to bring in all of the empty months that your prior to date covers so like I said we have a partial year for our year to dat which is 2024 it's up until April in the date set but if I put it alongside prior year to date it's going to bring in all of those empty months and I just want to see the months that we have already within the year to date so in my dim day I am going to add a calculated column okay so Lim date new column and I am going to call it in cast so this is going to give me a true or false um if I remember correctly it's going to give me a true or false value so it's going to be okay we're going to build a variable tab last sales date you probably could make that a bit nicer um with capitals but let's go with that so Max and we're going to Max the date in the fact table new line new variable this is our last sales date prior year e date and we want the bring the other variable in last sales date minus 12 minus 12 months and then what we want it to return to Us return enter we want to see dim [Music] date last sales date prior press enter okay so that has now worked we can see in in our column we have true false and now we'll be able to bring that in as a filter filter on true okay so now we have that column built in in past we can use that in our prior year-to date measures the last thing I want to do in terms of any additional tables is I want to bring in a values table and this is what we're going to use within our switch measure so what you need to do is go into enter data for columns I'm just going to put value values and the switch is going to be between sales gross profit and quantity I'm going to name it SLC for slicer values just load that in terms of modeling that's everything done now so we can really move on to building the Dax and the measures what I like to do is I do like to have a measures table and I do think people call it calculated groups but I may be getting that wrong and I simply go to new table press equal put my name in enter and you can see now it's at the top and I just add my measures here so what I'm going to start with is I'm going to build my sales gross profit quantity those base measures that I need for my switch and my prior date measures so I'm just going to write click new measure all I need to do for this they're very simple measures sales is sum and I need to do sales for my fact table and press enter okay so that's the sales I'm going to do work my way through them so now we have quantity it's the same thing sum quantity close bracket enter so we need to make up those values that we we're going to see in the switch measure uh to get to gross profit we need to understand the cost of goods so for cost of goods it is the exact same as so new measure right click new measure cost of goods sum cogs and then close bracket and now we can make our gross profit so for gross profit all it simply is so new measure right click new measure gross profit equals and it's very simple it sales minus costs enter right so now we have I am actually going to hide um my cost of goods I do not need this anymore I'm going to hide that and I'm also going to hide my column gross profit quantity sales so those are the bits of all of our switches we will end up making two switches um but to get to that point we need to know our year to date and prior year to date for each of these so there's a few more just between now and then let's start with our prior year to date for our sales right click new measure going to name it prior to date sales equals so we're going to calculate sales same period oh sales sorry same period last year and then we're going to bring in our dim date then we're going to close the bracket comma we also this is where our in past column is going to come into play so dim date in past and we need that to be true we can go ahead and close bracket and again this is where chat GPT can come in handy and clean it up so you can impress everyone there we go I'm going to use that press enter we can start bringing these into a visual or a table just to make sure that we're not getting any errors let's bring them all in we can see prior to date gross profit quantity sales prior to date because we don't have a date set um looking good no errors so I'm going to go ahead and all I'm going to do is copy right click on measures new measure and I'm just going to do the exact same thing for quantity oh and I'm going to switch out sales for quantity enter okay so no errors good to go I'm going to right click on measures new measure and then the final one is the gross [Music] profit bring it in looking good so we have the three that we need and now we can move on to year to date so this will be using the total year to date um Dex so year to date underscore sales equals total year to date here we go sales comma and then instead of using the uh the dim date I'm going to use the fact date and this is through trial and error which is a majority of the time what I'm doing on powerbi actually going to copy all that and I found that when I used the dim date it just carried on for the entirety of the year even if it's a partial year which is not what I needed so using the invoice date it stops at the max invoice date which is exactly what I need so that works and it's different to Prior today which is always good when you're getting different ones uh for the different measures uh okay year to date quantity quantity enter new measure just going to copy that in and then finally for the gross profit oh and switch that out for gross profit okay so no errors all looking good I'm really happy with that now what we can do is build our two switch measures the switches are going to switch between so for example one of the switch measures will be prior today and it's going to switch between sales quantity or gross profit so right click new measure what we're going to do is I'm going to call it I'm going to do switch prior to date okay shift over I'm going to build a new variable and this is going to be for our selected value equals selected measure selected value sorry um so we're going to do the selected value for the slicer so it's going to be slicer value values so whatever we've selected within that within that slicer that's what it's going to come up with and then we're going to do far tab um result I want to put this is what we're going to get back equals switch expression and we're going to refer to our selected value tab new line and our value one is going to be so this is what is in the slicer so when it says sales what's it going to give us and what we want is prior to dat sales I'm just going to copy this line CU this shift enter and then we can go [Music] quantity quantity and then new line and gross profit otherwise blank okay I'm just going to return result this is a habit of doing the result like this um but you don't need that second variable uh so you're going to pull it in it should be blank nice because we haven't selected anything so let's just test this out we can build this is our slicer our value put it in a slicer and if you select we should be seeing ah nice so gross profit prior to date gross profit quantity nice and sales okay that's working so what we need to do that's the one of two switches and they're almost identical so I'm going to copy it right click measure measure new measure just going to paste that in instead of py TD it's going to be here to dat and then all we have to do is go through really we just have to take off those piece in each of the measures the way we've done it and press enter okay so let's bring that in and we should be seeing so let me just simplify this we should be seeing these year-to date values so with sales selected we see that's correct quantity that's looking good and gross profit right cool so now we have our two switches that we need I do want to bring in a comparison actually let's keep that table we can take all these out leave our switches in and what I mean by comparison is I want to know the difference between these two that's why we needed that in past year because they need to be uh comparable when we look at them in tables for our comparison measure so all I need to do is simply take away this switch your date from prior to date so I'm going to right click measures new measure I'm going to call it year today versus prior to dat equals and I'm going to bring in my my slicer year to date tap minus slicer prior to date and press enter and let's see what happens oh let's bring it into the right table there it is and we get minus 10 million quantity that looks about right and sales yeah so those are the three key measures that we need and you can see the way we've had to build them up so now we have our key measures I am going to go and just clean them up uh so I'm going to take all the basic ones the cogs gross profit quantity sales believe that's it and I put them in a display folder so that's just going to be called base measures enter and then I'm going to put my prior to dat all in the same column prior to date our year to date YTD and then I'm going to put the switch and the comparison in a single one there we go so now everything's been organized I think that's pretty much everything we may have to build one or two measures throughout um depending on how we feel and what visuals we build but I'm pretty happy with that so we can get into the design of the layout and the visuals so let's jump into the design part of the report and what I'm going to start with is the background so by default it's a white screen but what I tend to do with my reports is build in PowerPoint a background so it's an off right back with an area at the top for header metric I take a screenshot and then I load that into powerbi that screenshot so let me just find it I click on the background I go to format page canvas background browse for my image which is background you want it to go to fit and you also want to bring the transparency way down so now you can see I have that separation at the top it's not a shape it means every report I use this in is going to be exactly the same so it's a bit more seamless so once I've brought that in I can start kind of figuring out which visuals I want so I know I want for my header metrics to use the new card visual it's probably going to sit like that and just for examples I'm going to bring in let's just for the example year-to dat sales prior year to dat sales we're going to bring in some examples these will change but I just want to see how it's going to lay out I will have a slicer for my values here so this is so where we can flick between those switch values and I can even start by putting the field in we'll reformat this so it fits in nicely I will need a year slicer so I'm just going to copy that bring it over actually I will probably use the old slicer version and I'm going to use use dim day go to the hierarchy and just pull in the year take that values out let's add probably a drop down for you nice I think that's good that's just reminded me selecting that let's we actually have to build the relationships in the back of the model uh so before we do anything anything further this we're all just figuring it out as we go long oh my goodness so the account ID needs to go to the account ID and this is why ah see this is a good point many to many we don't want that um so it's interesting why it's an account ID I don't think it's going to impact the data that we're using it for um but it is interesting why is it many to many that's something we can come back to if we find that it's affecting our data in any way dim day can go to date time I think that's pretty much it so now yeah we can see there's a difference in one more flick in between them and yes 2022 no prior to date that makes sense because obviously we have no priors of invoice sales so I am thinking I want a few different visuals I really want um not very numeric heavy um these are going to be for a portfolio project you really want eye-catching visuals that makes sense so I'm going to keep it the value selected on sales so we can start bringing in the switch and seeing how they impact so we have our prior to date let's actually go for year to date for example and then let's just bring in countries we're just trying to see you know what looks good what adds values to add values to an end user you don't want to be building random visuals you actually want it to be quite impactful I do want to bring in a wful chart it's very commonly used within my current organization I think it brings a lot of value especially when you have that prior year-to dat um versus year-to dat measure so I'm going to bring that in and I want to look at the date the data probably let's bring it in on for the category of the month so we can see how it's going over the year but we can also bring in lots of different we can even by country so then when we do our drill down we can see you know which countries are contributing to those months of major decline in Prior year-to date versus year-to dat comparison and then also what are those contributing to major areas of growth within the year okay so let I'm pretty happy with that obviously everything needs to be reformatted but it's a good start I do really like the line stacked chart so yeah this is our line and stacked column chart I'm going to be bringing in the year to date for our switch on our prior to date uh the prior to date will be in the line and then we want to look at it on a monthly basis but we can also bring in the quarter so wait let's bring that in like that so we can drill up and down I do like having the quarter below a lot of companies have uh fiscal years that start in other places in the in within the year so they may start in October so it's sometimes good to have those quarters so you can understand straight away which fiscal quarter you may be in and then let's bring in a little bit of a column Legend a little bit of a column Legend okay so I'm going to be doing product type I do like that or should we do product size no product type I'm happy I'm not too sure what I want to do this space so let's focus on these aspects for now in terms of the slices we can reformat these we'll bring in some conditional formatting for the header metric and we can actually now bring in what we want our header metric to be now we've got this value slicer here so it will be the switches and the comparison and now that I'm thinking about it this is a performance report and what does companies need to know they need to know their gross profit at all times not their gross profit value but probably their gross profit uh percentage so I am I do think I'm going to bring this in so what you want to do is go to measures right click new measure going to call it GP percent equals oh and then all we have to do for this is divide gross profit and I'm going to use the gross profit um not the year to date or the prior to date because we do have that year filter and we're going to divide it by our sales so pretty straightforward easy one to bring in um but it is a lot of importance on GP perc so big one so we're going to bring that into our het aetric you can see it's making a scroll so we will have to amend that here so layout it overflow or call out values single row cards Maxim there we go so this is the new cards visual there's a lot more options than the old cards um but it's the max card shown you want it to four for that so now when we switch between our GPU stays the same but we obviously switch between um what our switches are seeing so now we look at gross profit quantity sales and the year to date best prior to date is also amended okay quick break here if you're enjoying this video and if you're getting any value out of this video so far then can I please ask you three simple things number one can you please subscribe to the channel it helps the channel and everything I do grow more than you know number two please go check out my patreon where you can find more projects like this more detailed content where I dive into well really any Topic in a lot more details so make sure to check out my patreon if you want to and then number three is check out my ultimate data portfolio where you can actually include this project of yours to add to your professional brand and value so quick plug back to the video okay I think we can start cleaning this up now we have our main visuals once we get to this point down here we can figure out what we want to bring in um yeah let's focus on the formatting and the conditional formatting now so I am going to start with our values here um I do want it so they're all above each other so I'm going to go to layout I do not want three columns I want one column three rows I'm going to reduce the space between them actually should we have one maybe have one between them I'm going to take the title off and the header icons and the background take all of that away get rid of that call out values I'm going to reduce the size of the font just bring that to the side and let's just work away so this is on the for the call out values you have the option to look between default hover press select I only focus on default and hover so I'm going to go to the is that the labels no oh the buttons that's it I'm going to take the Border off and I am going to change the fill color actually actually just that's just reminded me I do not like this theme and I need different options for that fill so let's go to view theme I'm thinking blue I always go for blue it's just a nice clean clean connotations so blue go to the colors I am going to go for the lightest gray but I still think that's a bit dark so more colors we can just bring that up till we find something I really want it there we go something like that nice the padding also in these options I want it narrow I think that sits really nicely there that about like that and I want my accent bar but I want the accent bar because it's side by side a joy to the Head metrix I want the accent bar on the right just to give it a bit of Separation okay so I am happy with that um for the state I'm going to go to hover everything can say stay the same but for the fill I do like it when it changes so as you can see it now changes when you hover over I think it needs a little bit lighter that's nice okay let's go for that and for I'm just going back to the default the the accent Bar's a bit too dark even lighter nice I'm happy with that and then when it's selected it's really dark so fill we can go for like a gray that's yeah I'm pretty happy with that but feel free to change it however you want so now it's looking much more clean now the year I'm going to keep that I feel like it's self-explanatory the year do we need a title we could change it to um let's turn the header icons off go to the visual slice a header and we could put YTD um there we go CU all of our measures refer to year to date so let's just keep it consistent um I'm also going to reduce the size of the header and I tend to take turn that font color down a bit make it a bit more minimal cool I'm happy with that right let's move on to the other head metrics so lots got to change here I'm really happy with how these are I may just reorder so we have our year to date first our prior to date versus year to date in the middle prior to date and then at the end gp% we can go ahead and just rename these here to dat prior today GP per fine and now I may have a lot of changes for this so I go to the general options turn off header icons turn off background I go to the visual the layout's absolutely fine I'm going to shut that call out values they're way too big so let's just reduce that I'm let's try 30 that's nice I'm happy with that the color and this is on all of them so this is affecting all that's fine I'm going to make it gray I do like it gray um yeah that's nice but one thing I do want to do I am going to conditionally format uh one of these um specifically the year to dat versus prior to dat and we can you bring it to the series The Right series then you go to conditional formatting on the color for the font and you're going to bring in a rule the rule needs to be on the year to date versus prior to date if it is let me get this right so if it is equal or smaller than the max or equal or greater than zero I want it to be green or should we make it blue consistent with the theme it just goes against convention which is it's not the best but for this example because we're going off Aesthetics we can keep it like that the next rule is absolutely fine greater or equal to minimum or less than zero I actually want this now to be red so this is for the font color I tend to pick a darker one I don't like that blue let's go for that kind of blue okay so we can see straight away minus it's now red nothing else needs to be changed let's go to the layout no not the layout the label we can do the same for the label um I'm going to apply the exact same rule just so it looks nice and clean we're going to do Max anything equal or less than we want it to be a little bit lighter and then for the minimum we again wanted a little bit lighter than we just chose that rule looks okay we can see nice so we go to the cards and straight away I'm going to go to the year to date versus prior to date series the background I'm going to conditionally format exact same rule except these colors are going to be much lighter so we're going to bring in our Max greater or equal I mean less than or equal and then we have our zero and we want it the lightest blue and then the same for the red we want it up here okay I like that so for everything we go to our cards make sure our series is on all now I want to remove the borders I want the background to be that really light gray and I also need to get the the corners rounded so is this let me find where it says shape is it cards ah shape under cards is rectangle I wanted rounded rectangle I tend to put it at like seven I think this makes it look quite modern a bit more fun and I'm just going to Center that into that header area we can even make it a little bit small smaller I'm a bit particular when it comes to these things so there we go I think that's starting to look really nice we have a lot of blank space so we could really condense this we also have some formatting issues so we see GP percent let's just make sure that's a percentage nice and we also have obviously you just check that you're happy with the um how the values of to summarize so these are in millions that's thousands make sure you're okay with that I'm pretty happy just going to keep those aligned like that so straight away looks 100 times better in my opinion um uh so I'm going to move on to the the main visuals for these I am going to go to the tree map first and at the moment we do have year to date in for right now is sales but what I want is the comparison measure instead I'm going to show you what I'm going to do because I do want to convey value in this performance report even if it is a portfolio project and what I was thinking for this one specifically is to understand which countries are in the bottom 10 for their um year-to date versus prior to date because then we can see those that have significant significant decline in sales versus the prior date so to do this I'm going to go to the country in the filters Pane and I'm going to go to top n I'm going to change top to bottom bottom 10 and buy the value year to date versus prior and straight away I can see which ones on those bottom 10 nice so this actually gives me some value and what I can do now is add a bit of detail let's see what kind of detail we can get maybe product size no um perhaps we leave that maybe we bring in some data labels and we can see what's bringing them into that significant these significantly low countries um so we have minus five versus prior year to dat gross profit or sales and we can see why they're in that bottom 10 okay I'm really happy with that I feel like uh end users would get a lot of value so I'm going to move on I'm not going to I'll come back to this one in terms of formatting there's some things I want to change uh but for now we'll move on so our waterfall I don't intend on making too many changes I do think it's key to sorry I've just been stopped cuz I saw this was blue and obviously it's against convention so I was like what why is it blue um so maybe consider whether you want to keep that blue or green I may change it to Green at the end so for waterfall this is basically saying what's gone into this comparison how this is the difference between the two so we can see 177,000 between current year to dat and prior year to date and then what has contributed to that and in what way I will keep this green and red I think that's very key um but I do think we do need those data labels on to just give us that additional numeric information uh that the end user will need for this kind of visual what I will also do so we have currently the date uh by month and Country I think it would be key to bring in product type and then even go down to product name so we could drill down July we can see C has had a major decline uh specifically to outdoor plants and then these are all the plants that have been sold in Canada straight away I would be looking at I'm not going to try and pronounce that plant name but this plant and then comparatively I'd be like oh this plant's doing very well why is that what counts is this are they new are they old um and then the same for the other one so it's it adds a lot of value to end use it this way let's draw back up nice so in terms of changing anything I'm actually really happy with that visual I don't want to make any amendments moving on to our stacked column line CH what's it called line and stacked column chart um I'm really happy with the quarts being like this we can see when we look at 2023 the full year and I think that's a really nice way to keep it organized um should we be putting on data labels will it get a bit busy what we can do is instead of expanding let's just drill down to the next level it's still I do think it's going to be a bit busy I'm going to go to the format Visual and then I'm going to go to the line I do think that needs to change color because it just Blends in we can bring it in with a maybe a orange or green or a red I think red that's I like that so we have our prior year so we can see in January year to dat is is is lower than the prior year to date so we can see that in that specific month in April year to date we exceeded the prior year to date so straight away I'm seeing and taking Insight from this visual I am going to add markers because I think that always looks quite fun looks quite pretty and should I add data labels I just don't think I'm getting the value from that but I do think if you were then to go and expand that you can see you know what that's referring to what I could possibly do is total labels remove the data labels for um the indoor outdoor but keep them on propri to date okay let's see how that translates here it is messy but but when you do go to expand I do think you'd get value from this the tool tip will tell you the breakdown um so I do quite like [Music] that let's see if we can for prior to dat is there any position under yeah okay let's stick to under and then with the totals um possibly or maybe instead I'm just trying to think what we could do we have the prior dat would it be worth bringing in the comparison I think we get that enough elsewhere so we can find that elsewhere so I'm going to keep it like that maybe we expand down just trying to make sure the format's good and then we can find out what our mystery visuals going to be I do have an idea from what we've been working on um I'm trying to get as much space as possible so no we want that but maybe we don't need the title okay so and let's just finally rename our AES so that's year to date and obviously with a swi it will be difficult to have a suitable name but I am just going to put value year to date and then line is value prior to date okay I'm happy with that so for the final visual I have been thinking and it brought me back to the GP percent and the importance of it that companies place on it and I have been thinking if we can understand where accounts lie in terms of profitability so a way of doing that is by segmenting them and we can do it very easily in a scatter chart or a bubble chart or scatter yeah I know different things call them different things um so scatter chart we have our GP percent I want that on the Y AIS and then I'm thinking for our xaes we can put our switch so our switch year to date and then there we go on our x-axis and then what we can do is bring the account and obviously it's going to look crazy there's no way around it there's a lot of different accounts but the way we can minimize this is bring in the zoom sliders and straight away even with this CA chaos here we can see a majority of our accounts we're on quantity um there we go they are saturated around a specific quantity we have only outliers for major invoices or accounts that are purchasing very very high quantities past a certain threshold so we can understand our own threshold and then then what we can do is so with our GP perc if we have a target of say 65 we can already see those that aren't profitable for us or helping us towards our profitability goals so it's very easy to segment using these scatter graphs scatter charts apologies what we can also do is prioritize so if we have low quantity for example you're on quantity and you have accounts that have really really high GP so let's find our highest but their quantities aren't as high as other or the average which we could also put in then maybe we could focus on those for sales tactics things like that so it's a good thing to explain when you're summarizing your portfolio project um why you've brought this chart in I'm just going to see if I can then bring in a average line add line that's nice so now we can see what below average sales yep so we we want that on the sales the line let's make it deep red to match the top one and then the series we can go and rename value year to date so I really like this visual now this is like my favorite one now and I don't think there's a lot of formatting that we need to do okay so I'm really happy with how this visual has turned out I am actually going to add a average line for the GP so I'm going to add a second average line but it's going to be for the GP percent we can see it's just gone across there just so we can Benchmark the average uh this will also help with segments or already I can see you know depending on how youve scrolled which segment or which quartile they're going to fit in so straight away it's giving you Insight that's all we can really ask for for these visuals okay so I'm going to keep it like this I think the final thing is um titles and just I tend to edit the borders um not a big fan of the white boxes but we can have a little bit of trial and error so with titles they have people have to understand what they're looking at straight away so year to date this was looking at the top bottom 10 bottom 10 it was for year to date versus prior to date and then I add a oh I add a line I don't know what that's called um country okay oh new keyboard all going wrong I will reduce the font size I wish those little buttons would go so we can see it reduce the font size and I'm also going to make it a little bit gray make it a little bit darker than that there we go I'm going to remove the background how's that looking bottom 10 year to date versus prior to date by country I think that that looks really neat and tidy there so I'm really happy I'm going to in increase the size of this and I'm also let's go and change the title so this is looking at year to date versus PRI to date by month okay wait this changes doesn't it yes so one thing we can do for this that we can do for other titles is we can add in a dynamic title so go to your measures right click new measure this is something we called a title measure at work so I'm going to call it waterfall title and let me just put little underscore at the front it's going to bring it to the top of the table so water for title all I'm going to do is the I'm going to bring selected value Tab and and I'm going to put slicer values so it's whatever selected in that slice is going to come up and and then I'm going to space after the quotation mark I'm going to put yet date versus prior date and then I'm going to do oh I am going to do the line thing that I can't remember the name of month and I it's country product so people know that they can drill down into this and then I'm going to close that and let's press enter and then selecting the visual you go to the general title and you're going to press the conditional formatting for the field value and you're just going to select that measure there so now whenever you're selecting through it will change so it's always providing that additional bit of context I am going to keep the white box I think it's nice I tend to put a little Shadow on it but for now I'm pretty happy with that I'm going to come down to the scatter chart and I'm going to see what it looks like without a background which is let's see what these two look like without a background these this is my favorite bit I spend too much time doing this we could do it like that um this is obviously personal preference um yeah okay I I can get behind that so we're going to do the same thing as the uh World forall chart I'm just going to copy that and make a little bit of amendments in a new measure just paste that in and this is going to be I'm just going to call it column chart title selected value it's going to be instead of year to date versus prior to date it's going to be year to dat uh and prior to date by month see how that looks we have to select the visual go to General title conditional format and then we just want to bring that measure in quantity each and prior state by month nice ah did you just see that so let's just there it is okay so maybe we keep the scroll like this nice because this one was going blank I don't know if you saw when you press sales so just keep that in mind when you've got your scroll on so value year to dat and GP % by account instead of that I am going to have it by um we probably let's just do a dynamic title again I'm going to have this as a specific like analysis so it's going to be account segmentation instead of just referring to what's within the visual so I'm going to call this scatter selected visual um okay so that may change that order I'm going to go account segmentation account profitability segmentation it's a good buzz word for these projects and then I'm going to look at bu and then with a space after it just one space I'm going to switch this round so instead of it being at the front I'm going to put it here so by GP percent space because you need a space before your value and then you're going to put your and between the two and that right let's see how that's going to look select the visual title conditional format and then you want to go to that measure so ah what did I do oh I didn't dig it out let's just amend that by gp% account profitability segmentation by GP and oh let's take that by CU we haven't got that out in anything else and space I that's good now yeah I'm really happy with that so the final thing that I think for our formatting I think everything else considered I say that every time and then I see something I want to change I'm just mindful of your time um so I am going to go to title reduce the size change that color I do the same thing I don't know if you can hear my dog growling in the background apologies if you do um reduce the size I'm going to do the same here but it's one second I'm going to tell my dog I've done the titles they are looking good I I'm realizing that I need to do the axis titles because that will bother me if they're all in such harsh colors um I'm going to reduce the size I'm currently on the waterfall chart on the x-axis and I'm going to go to the Y change the titles to be bit lighter bit smaller and the exact same with these so we're currently on the stack chart make them gray make them small I'm really happy with what the namings are and then finally here I wonder if I can change the color of the zoom no that would be too easy and we certainly don't want those labels we could it's just too crazy so let's just stick to the titles it did sometimes gives you a lot more space as well so as long as people can read them I think that's all that really matters let add that that's fine going all the way to the end I'm happy with that great I think we're at a really good point the final thing and I say the final it won't be the final cuz there's always something else um that I want to do is add a title so straight away when you go on if you haven't used this report how will you know um how to use it or what it's straight away referring to uh in terms of the year what is the year to date and what is the value I'm actually just as I'm saying all this I'm messing around um just put that there so I am going to add a report title and that's just going to sit in this nice space here so I have tried to as you can see reorganize it a bit so I have everything all the space here not my favorite layout but we oh my goodness let's take off the background color for this slicer okay I can get behind that that's nice that's okay we go down we have our options you can also format that if you want but for now I don't think it has too much value great so let's add this final title so let's just copy and paste one go to measures right click new measure okay so this is going to be report title report title and then it's going to be plantco performance okay take the space out the plant code just the name of the company put you there selected value we can even put it actually that would be quite nice is if we have plant C we have an selected value in terms of so plank code sales plant code quantity and space let's just make sure this a space after code space performance so that would be plantco space quantity performance and then what we want is and and and we want to bring in selected value oh date I'm just thinking so it's going to be selected value can we bring in instead of selected value it'll be year and the year of the selected value would it be selected value um date yeah let's go selected value just figuring this out and date dim or dim date I don't know if that's going to work but let's have a go no that's not come up why is that not come up report title year wait if we just go apologies dim date and then we pick date and then we go yeah that was it that was what I was missing it was staring me right in the face watch it yes okay now we've got it press enter perfect so plant go quantity performance 2023 let's reduce that in size probably like 20 nice text wrap yes we don't want the labeling and what we can do is if we just put it right there let's close this no there we go see if we can change this so call out value let's make it a little bit lighter is there a nicer font no that's probably should have just stuck with it um I should have just stuck with it where has it gone dead okay happy with that we do have a little bit of space we could probably put the year even if we want to put the Year I wish you could decide on where this goes and 2023 I don't think we need that header I'm saying that because now I just want that space um so we can very easily pick um which where which year we want to refer to as our year to date and it is probably a good idea to filter out 2022 um just because we don't have any year to Prior year to dates for that so a lot of the report becomes obsolete um we don't want that so just trying to get a good layout it needs to be single select cool great I'm so excited about this and I said it in the beginning when I made this visual I am changing that layout that card because um the blue just was throwing me so I'm going to do that now and I think we can review and then publish so it's gonna go green oh I don't like doing it green there we go okay Green's fine and then we just do that for the call out values as well change that nice okay it looks fine I'm actually I'm not sad about it I'm a little bit sad I love the blue so on theme and then you just want that cool ah a little bit too light make sure your end users can read your visuals so I think that pretty much covers it I'm really happy I'm going to rename so performance if I can spell it right performance report we're going to save we're going to say portfolio project okay there we go we got we got there in the end uh we're going to save in there uh let's call it performance report save it down nice okay just having a look around one final time I'm going to try space these out I'm such a sucker for having things equal that's not equal okay right I'm just going to stop messing with it now we have our bottom turn quantity year to date versus priority date quantity year to date and prior to date account profitability our hom metrics and our lovely switch measures I'm so happy with this let's go ahead and publish just going to ask us save I'm just going to publish in my workspace and let's open it up okay so as an end user we come here we look at our current yesterday which is 2024 and let's focus on gross profit our gross profit major declines in March and April if we want to look at April I can straight straight away see Canada is contributing a majority to these um declines versus prior to date and a majority of it is coming from landscape going in we can see exactly what it is so straight away I'm getting value from this if I want to see um month by month I can see February that was our biggest month and it exceeded a prior to dat what we were doing last this same period last year so February is a good month maybe we review what we were doing in February see if we can continue it in the further months so now we can look at accounts specifically let's focus on this area right here we can see it's got above average GP percent especially these ones but they have quite low oh oh let's look at sales they have quite low sales so maybe we focus on those build up more rapport with the accounts get more sales in these profitability ranges so we can really segment our accounts straight away and then we can focus on maybe the strategies over here for these countries that are in the bottom 10 for year-to dat versus prior today Canada Colombia Croatia Germany these may be where we focus new strategies and rethink how we're approaching the markets I think that wraps everything up I'm really happy with how this turned out and I hope you guys go out try it for yourself write up some great projects and add them to your own portfolios for now that's it and I will see you in the next one bye all right one last thing obviously the project is done so if you liked this video then you're probably going to like these videos right here make sure to check them out and I shall see you in the next one thanks so much for watching [Music]

Channel: Mo Chen

Views: 10,256

Keywords:

Id: BLxW9ZSuuVI

Length: 74min 37sec (4477 seconds)

Published: Wed Jun 05 2024