Master Scatterplots in Power BI: A Step-by-Step Tutorial

Scatter Plots in power bi can do a lot of things this one here lets you select what you want dynamically as the y-axis and lets you select how you want the size to appear and it can also let you select the colors to Cluster the different points based on your selection we're going to be going through how to make this step by step today so follow along and let's check this out so this is actually part of Enterprise DNA's visualization workout from Gustav Dudek I'm going to link this website in the description and if you go here you'll be able to download the Excel file that I'm using for this workout as well as instructions and a community of other people who are also developing this report now all the instructions that I'm going to follow are in the actual report itself which is also in the website you can you know it's like see there's a link that will take you to this report and we're just going to Simply get started okay so I've already downloaded the data and I have loaded it into my new power bi desktop instance here and I'm going to show you the steps that I have taken to make this scatter plot this might be a little bit of a long video but bear with me I'm going to take you through every single step of the process now the first thing that I'm going to do is I am going to create a new table and we're going to call this measures and it is simply going to be a table where I'm going to keep all of my measures and we're going to go back here and maybe make this a little bit larger so that you can see exactly what I'm trying to do so the first thing that I'm going to try to do is make the metrics the gross profit and gross margin percentage which will be calculated based on the data set the data set itself is actually very simple it has some information about the date what the clients you know it's like number ID is um the client's name the business ID the business name uh the unit sold revenues and the cost in and revenues and costs are in thousands of USD pretty simple data sets and in order for us to actually do what we need to we're going to first calculate the sum of Revenue now you can actually of course some of Revenue is uh something that you can just drag a column from the facts table that is has been provided and that will give you the sum of revenues this is what's called an implicit measure but because I already already know that the sum of Revenue is going to be used in multiple measures I'm simply going to make it into an actual measure that I write out this is called an explicit measure and you know it's pretty important to actually do so as you can see the sum of Revenue that I've you know it's like put in here is simply the sum of this column likewise I'm going to create another measure called the sum of the costs which will of course just be the sum of the cost this is important because since we're trying to create the gross profit and the gross margin we need these values because the gross profit is of course the revenue subtracted by the sales and if we want to calculate the gross margin it and the gross margin is a percentage of uh what is it the gross profit divided by the sum of the revenue and so simply need to you know to select this and format it into a percentage so once that's done we can actually start creating the scatter plot already so we have a visual with Revenue inside and I am going to throw in the gross margin percentage and selecting the build a visual on objects uh you know it's like formatting I'm going to select the visual types open up the drop down and select scattered chart now I've selected a scatter chart but as you can see it doesn't really look like the scatter chart that we have here so what I'm going to do is I'm going to you know take a look at this and I can see that there is another value that needs to be input into the scatter chart in order for it to work so we're going to go back to the power bi and we're going to see we're going to put in the client's name into this visual so I'm going to select build a visual again I'm going to add values as ad we're going to throw in the client's name and we can see that this visual itself this scatter plot here is starting to look a little bit more like the scatter plot here so if we have them side by side you can actually see that yes it is starting to look a little bit more similar but in that case let's move on to you know say the next Parts lines applied zero lines for both x-axis and y-axis constant lines so let's get on with that part here in order to do this we're going to need to click the visual elements and we're going to need to solo to select more options in the more options you can click on reference line and add a line you can choose the type of line as well it's going to be the x-axis and the y-axis constant lines and we're going to set them as zero in in order to make them look more like this specific uh you know it's like item I'm gonna do it in the same style as well so you can see that this is a kind of you know it's like solid gray slightly transparent so we'll just select a gray maybe a slightly darker gray we're going to select the slot style to solid it's in front that's fine I'm happy with what that looks like and then we're going to add another line a y-axis constant line and it's going to be the same so instead of Dash put it that's solid and as you can see that's uh the second part right here it's got those nice solid lines which are slightly transparent uh and that takes care of the second step now for the third step on the X Y axis revenues applied as x-axis and two other metrics applied as y-axis scale using field parameters so he he mentions that you can use you know it's a year over year change in that sales or any other metrics so I'm going to do exactly what he's done here as well so you can actually see make this a little bigger again you can see that this is the field parameter he has gross margin and net sales and depending on the selection the y-axis changes so we're going to do that as well the first thing to do is uh because we're going to do year-over-year percentage increase we need uh you know it's like some form of a you know honestly you don't necessarily need a date table but I'm going to add one anyway because it's pretty easy to do I'm going to select external tools and this works really well if you have you know it's like the external tool already installed but I like using Bravo for you know it's like tasks like this essentially what I'm going to do is I'm going to open up Bravo and I'm going to click on manage dates and in the manage dates what it's going to do is it's going to create a date table for me directly to use I'm going to make sure that I turn off the time intelligence functions because I really don't need those and I'm going not going to select holidays either but if I have dates it's essentially going to create a date table for me without me having to do anything at all so there it is it's done and you can see that now in my mod data model our data table has appeared so what I'm going to do is I'm going to take that data table and I'm going to connect it to the fact table using the date columns make a relationship and now I have a fully functioning data table friends if you haven't you tried Bravo yet really you got to get on it yeah it's the fastest way to make a dates table for your data model so now that I actually have a dates table and you know it's like as you can see it's basically got everything that I need from the actual dates to you know it's a year year quarter months Etc with this date I'm actually going to you know it's like generate the net sales uh past year and in order to do that I'm going to go back to the measures table and I'm going to create a new measure so I need this to go away this is one of the things that is unfortunately not the best uh the on object remains you know even if you're going through a different tax the tax you know it's a code should really be over it in my opinion so net sales past year basically what I've tried using the offset function where I'm going to try to calculate what uh for the revenue in the last year what is the uh so calculates the sum of the you know it's like revenue and offset it by 365 so a year you know it's like 365 days based on you know it's like the dates table ordering by the actual date so this is one of the ways that you're able to calculate you know it's like the past year I know it's probably not the most optimal one and of course you can use you know period last year instead but I wanted to give this a go so once I have the knit sales past year I can create a new measure that is going to be the net sales Delta percentage so this will be the year-over-year percentage increase so you're basically taking the sum of the current year Revenue subtracted by the revenue in the last year and dividing that by the last year and that way you will have a percent increase if you want to know how to make this uh you know like Delta sign essentially in you know it's like uh on Windows computers what you need to do is you need to hold the Windows button and the semicolon so this button right here and if you do that then you get uh you know it's like this window that pops up and there's a button here that says symbol so if you click on symbols it should be something that you can you know click select if you just scroll down I'm sure you'll be able to find it I've used it very recently so you can actually see that it's here in recent but I think in math symbols yeah because Delta is yeah so you have a you know it's like Delta symbol here that you can you know use and that's basically how you get it out very quickly so uh I this is basically you know it's like a percent so I need to format it into a percentage and then we're almost ready to go so the expected behavior is that I will have a slicer that will be able to change um the y-axis based on my selection so what I'm going to do is I'm going to go to modeling new parameter fields and I'm going to name this parameter we're adding axis and I'm going to select the net sales Delta per uh past year percentage and the gross margin and once I create this also make sure you have the ad slicer selected and then it's going to have it's going to generate the the slicer itself and I'm going to need to change this scanner plot the y-axis from Cross margin to the y-axis parameter that was created and now I have this y-axis parameter and it's going to allow me to switch between the gross margin and the net sales you'll actually uh you know say very clearly see that this doesn't look the same as you know it's like this when net sales is selected but that's because the date is currently being used as a filter so let's add those right now uh the date filter should be year and month so right now 2022 has been selected so you can see that now corresponds to the actual visual itself if we want to make this look more like uh what's you know it's like Gustav is actually created I'm going to make sure that I go to more options size and Style no it's a slicer settings and I'm going to have it be a drop down and select 2022. so you can see that's exactly how it looks and for the uh let me see the y-axis itself now it should function almost exactly the same but I also need to make sure that this is a specific item that has a single select because if neither item is actually selected you end up with uh you know take nothing selected and if nothing is selected it actually can sometimes cause issues make sure you have a single select going and you should not have any issues okay so that's step three for step four we're going to have average and median lines now we're going to apply uh either average or media lines for any selected metric to be displayed on x-axis and y axis with the line name and value so how you do that is you're going to select the item and go to the format pane once again and we're going to add two more reference lines so it's going to be add line and this line we're going to call you know it's like maybe uh we won't name it just yet but we're going to make it be an average line of the y-axis and you can see now a lovely y you know it's like access line has been created I'm going to leave the color as is I don't think it's super important right now but the data label you can actually select and you can see that a tiny blue data label has appeared this is a little hard to see so you can actually change the position above or under as you don't like you'd like and you can select the data value or the name or both so now you have something that's a y axis line that's why it's important to name these and you know it's like uh you know it's it's pretty cool to you know to be able to do this okay so I think I've made a mistake here I think I've been working on something else okay so we have the average line the y-axis line and these two are the you know it's like constant lines what I wanted to do is I want to work on the average line not that one so now I've selected the average line again and now I've created the data label now we show we're seeing the correct data label okay and I'm going to call it uh you know to give it average line you know it's like and it's showing a specific value and you can select left or right I think right fits better because you'll actually be able to see it this time and you can choose to have it either above or under I'm going to have it above for now and this is this is a pretty good average line and uh we'll go back and see how it looked like here average net sales with no decimal points and there we go we're going to need to create another um Aboriginal uh you know it's like average line for the y-axis as well and basically what we're doing is we're going to select average line and we are going to okay so I did select the average line but you can see that the bottom one was selected I think this is just a power bi bug and uh I'm going to select the series as the net sales past year and I'm going to make sure that the line is the same color as a as it should be and the data labels on and it's going to be the exact same it's going to show both and I'm going to make put this on the right so that you can very clearly see it and it's going to say the average net sales Delta last year percent okay fantastic so I'm happy with that it's a little bit wordy but you know it's like it shows uh exactly what I'd like it to show but this is one thing that you need to watch out for right now we have the y-axis net sales Delta pass here selected so I've created so you know it's like something for this but what happens if I press the gross margin and change the dynamic y-axis it's actually going to disappear so I need to actually go back and create another reference line so it's going to be add another line and I'm going to make this line another average line select it again and select the gross margin so it's just something that you need to know does happen but it's pretty you know it's like as long as you know you know it's like and you test it out you will never really have any issues in the first place and there we go now we have you know it's like some lovely reference lines that work for whatever the dynamic selection you have okay so that covers uh week four uh we're going to go ahead and do 0.5 item marker colors now this is a little bit of a tricky one item marker colors is a kind of conditional formatting either using a gradient rules or field value uh field value preferably but not obligatory conditional formatting can be based on any conditions rules that are analytically valuable from the report Creator's perspective now I am going to you know it's like hold off with this because there's something here you know it's like point 10 which extends this a little bit more so number five we're gonna wait just a little bit so number six uh applying Dynamic title either either as a native scatter chart title or as a text box with adequate names of currently displayed metrics so this right here is what you see at the very top depending on what is being selected either gross margin percents or the net sales past year uh you know it's like depending on the selection it actually changes the you know it's like title and also depending on the year uh you know it's like the date selected it changes uh you know it's like what is being shown uh right now you can also even show uh you know it's like uh hang on let me see this is there any other indications no it's only you know it's like the y-axis and the size that alters you know it's like the the text itself but you can see it looks really nice and works really well and I'm going to show you how you can do this with a text box so first of all I'm going to remove the title on this lovely scatter plot we have going on and I'm going to add a text box right here the reason we actually want to use a text box is simply because I don't think it's actually possible to get this kind of bold Parts in the you know it's like text from the title itself so we're simply going to try to get that going uh basically what I'm going to do is I'm going to make sure that I make the values first if you are trying to do something Dynamic make sure you always put the values first in the order that you want them right now what I want is essentially two different values maybe three different values what I'm going to do is the y-axis selection so y-axis name I'm going to save this as y-axis then I'm going to create another value it's going to be a date and the minimum date and we're gonna put this as in this format and then I'm going to create one more or the maximum date in this format so now you can see that there are three different values that I can use let's make this a little bigger for now and I one of the things that is a little bit annoying is that it's really difficult to work with these if you select the wrong thing for example everything can be removed for example but uh let's just go back let's see how that was selected Okay so this item here is bolded and then we have net Revenue I believe that's what it said net sales in kusd and now we almost have it yeah so one of the things that is slightly annoying is that it is very difficult to get these selections going simply trying to put in this item here and it's pretty hard by contractor okay so I'm also going to make this all of this a little bit bigger are also going to make sure that we format this properly so size and style will remove the background and make sure there's no visual border and now when I select this you can see that it all automatically updates based on what is being selected cross margin percentage and if I select a different year of the month January uh you know it is unselected or if I just select 2011 uh 2021 for example the date ranges change so this is a way to use you know it's like the values for the text to create a dynamic how would you say a dynamic scatter you know it's like title for your scat plot let's move this back just a tiny bit okay so now it's like perfectly that's great that's exactly what we want to see uh let's go back to here I think I want to change this to instead of sum of Revenue so I'll just call this revenues fantastic so now it's looking it's not exactly like this but it's pretty close I'm happy with you know it's like what it looks like right now so now we've completed the title we're gonna create the Legend and of course the axis titles number eight already changes based on the you know it's like metric selection so that's not a big deal number seven um The Legend here is applied is you know it's like as native built-in legend for the gradient or as individual Custom Creation Legends should indicate describe the color meaning now what's Gustav has done here is based on what you selected in the cluster segment you uh you know it's like he's put in a couple of things all together so the colors change based on your selection but also the legend here changes based on your selection and and you can see that this is a also in relation to number 10 the cluster segmentation for the conditional formatting depending on you know it's like what is being selected so I'm going to do all of this in one go and make and show you how to do all of this stuff uh you can even see that there's a legend depending on the conditional formatting complexity being done and uh I'm gonna I'm gonna show you all of this right now there is also one other thing here at the very end number 12 labels item names where there's a switch button where you select uh you know a switch button in order to show labels or hide labels I think this is the one that I'm not going to show you because uh that's simply a bookmark to you know it's like show either one that does have you know it's like the um contractor you know it's like name selected or not but I think that I'm going to show you this large part of how to do the conditional formatting in this way and of course before that we're gonna do size size is uh you know it's like a dynamic selection as well where you can select either the size as the service is sold or as none this is a really cool thing that he's doing here and I'm going to show you how it's being done essentially if I take uh you know it's like the size here and I add from the fact table the I believe it's the unit sold you can actually see this is basically what is being shown this is uh you know it's like the actual size and everything matches you know it's like it's almost identical and you can see that but that being the case how do we make this into you know it's like something that you can select well I think the easiest way to do this is to make sure that you are creating a very very simple uh you know it's like uh it's honestly very simple how how to do it this isn't something that's you know it's like I would normally do but in measures we're going to create a measure called No size is equal to negative one and from no size and the sum of costs we're also going to need of course we need to create another measure of four the sum of the units and we're going to create a field parameter yes another field parameter so we're going to call this one side selection and we're going to throw in the no size and the sum of units now you can actually see that um here it's being called none and services sold and if you want to have the exact labels you can actually change them here and if I create this basically I'm going to just need to do what I did for the y-axis as well make sure I remove the size and make it into the size selection item and once the size selection is done if I select none it will just show a you know it's like the regular size but if I select Services sold it's going to show the big size of course let's go and make sure to format this correctly because I want to format this in the same way as this slicer I could just select the slicer click on format painter and select this and it should be you know it's like it should show exactly what I wanted to show in the correct way so that's what I'm going to do all right let's you know let's get this out there ah it's not perfectly aligned oh whatever it doesn't matter okay so now that I have this done I'm going to finally show you how to do this fantastic really really fantastic uh you know it's like method of selecting multiple different colors based on you know it's like the selection and also have a uh a legend a color Legend based on what you've selected I think this is a really really you know it's like a great technique that has been shown here and I'm going to show you how to do it the actual secret lies in honestly it's a table and this here is the table itself this table is something that I you know it's like scraped from this you know it's like power bi reports by taking each of the different clusters uh the different selections that you can have in the you know it's like slicer here to select the different colors and depending on the selection taking out the different conditions that you see here so there's five different clusters non-gm percentage levels GM percentage above below zero and um change percent uh you know past year percentage so year over year percentage change above below zero and the business line itself so or each of these items I mapped out all of the different uh parts that would be the segments of uh the cluster and I gave the segment uh you know it's like an index and the cluster is an index I also gave each condition its color bait and I I actually scraped this from you know it's like the power bi report as well I use uh this extension call on Chrome called color pick eyedropper and if you open that up and you hover over a specific you know it's like a value in the in Google Chrome you can see what the hex code is and it's actually really important to have the hex code here you know it's like in the table if you want to make this uh you know it's like Matrix happen in this way so what I'm going to do is I'm simply going to copy this table that you know it's like and I create this before and I am going to go to enter data in the power bi and I'm simply going to hit Ctrl V and here I'm going to call the table cluster slash segment and I'm going to hit load and this is simply going to load these units like uh you know it's like this tiny table into you know it's like my system and now comes the you know it's like more difficult Parts I've already created this measure and you know it's like because I already did this Workshop slightly before and this is what's the measure that I'm using looks like I've called it scatter plot colors and it has uh first it first finds a variable which is going to be the selection Index this is going to recommend represent what segment is being used and from this uh you know it's like segment index it's going to calculate the color based on you know it's like this segment segmented index so depending on what's you know it's like being selected here it's going to tell me which color is being used the way that it does that is that for each of these you know it's like individual items okay so I don't need to actually do this I think I already have this up here so the way that I'm doing this is that for each of these individual clusters that are selected the condition that is going to you know it's like hold true whether or not the you know it's like gross margin percentage is uh you know it's like above 45 percent or as fits above 25 to 45 I am basically giving that information to power bi in this Dax measure so if none is selected there's no other condition so the index is just one but if the GM percentage level is selected I need to know if the gross margin percentage is above 45 then it will be condition two if it's above 25 and not above 45 it'll be condition three and this is being done with the switch function goes through each of the different clusters finds uh you know it's a condition for the different segments it's going to be if it's above or below zero percent uh zero then it's going to find simply whether or not it's above or below zero anyway that's the logic behind the Dax function so once I have that I can go into the scatter plot again go back into the format Pane and if I select markers there will be a color aspect here under markers that I can select the function for for conditional formatting I select the format style as the field value and I simply take this measure scatter plot colors and once I've done that all I need to do is I need to take this table that I enter and I need to throw it into a slicer I'm going to have the slicer be single selection and if it is single selection you can see that it works uh depending on what cluster is being selected it's going to tell the scatter plot based on this tax formula what color I want the markers to be so this works let's change one thing though which is that the cluster should be ordered by the cluster index and if I do this then we have the exact same order that you see here that that's basically the reason that you have the index value in you know it's like this in this table because if you didn't have the index value it wouldn't order you don't think very nicely okay so now let's do the final part which is we're going to make this Dynamic Legend based on what is being selected this is essentially just going to be a very simple table I'm going to throw in a segment oh no uh I'm going to throw the segment into you know it's like a table here and I'm also going to throw in the segment index so now we have these two different items and I'm going to change it back into a table and I'm going to make sure that the segment index of the title I'm just going to name one the reason for that is that I just want this to be a tiny tiny box that I can enter in okay so now I'm going to go into the format the first thing that I'm going to do is I'm going to change the style preset into minimal I am going to make the grid into a white color that I'm going to increase to maybe is width 10 I'm going to go ahead and remove the totals and I'm going to make sure that the column headers is white there's no real way to remove the column headers now before in previous versions you used to be able to just make an item hit click a column name hit space and enter and it would remove it would make the column a space bar but it's not possible right now which is you know it is what it is I'm going to make the Border also White and now we have this here now we're going to add the colors to this you know it's like Index right here and we are going to make sure that the specific column now the cell elements is going to select is going to be selecting the series one and I'm going to set the background color and I'm going to hit effects and I'm going to set the field value to Simply Be the color um the color column from this table and if I do that and I do the same for the font color it's going to show me exactly what I want because it's going to reference the hex code that's being selected and that's essentially it now when I select any of the Clusters it will show me whatever I want let's maybe move this here let's move this under here and let's also in the view for selection also move this table to the back so that it doesn't bother with this okay so now when I select none it shows all items when I select any one of these items it should show me exactly what I'd like to see and that just about covers every single part of making this you know it's like visual of course there are some things that you know it's like you could do like making you know it's like the colors a little bit nicer making sure that there is a background that's uh you know it's like presents the reports in a slightly nicer way but I'm not going to do this that for this video I hope that's you know it's like you managed to learn something from the way to use you know it's like field parameters and a little bit of you know it's like custom formatting in and a little bit of this um text box value stuff in order to get a really Dynamic customizable Scatter Plots to use in your own reports thanks and as always have a good one take care

Channel: Power BI Park

Views: 25,183

Keywords:

Id: EMpebK1PzIE

Length: 39min 4sec (2344 seconds)

Published: Thu Apr 13 2023