Building SVG Charts in Power BI

with svgs we can build any visualization we want in powerbi and unlike with normal images we can bind our data to the different elements of the visualization and they're scalable so they don't lose quality when you make them bigger now there are many templates for charts that you can just copy over however to make them work with your model you need to know a little bit of how SVG works and also if you want to tweak the template or maybe you want to create your own well in this video I'm going to show you how to work with svgs in powerbi step by step let's get [Applause] started welcome to how to powerbi my name is boss and if this is the very first time for you visiting this channel make sure to hit that subscribe button to stay up toate on all of my videos in which I share everything I know about powerbi all right now let's talk about svgs scalable Factor Graphics now how can we work with them in powerbi now first of all we can just look for SVG images download the image and use that in powerbi that's one option but we can also well use the code for that SVG image copy that over to a measure and then we can bind it to our data all right now let's go over this step by step and how you can figure out how to create your own SVG all right now for that we're going to start off on this website called SVG viewer def all right now here there's a whole bunch of svgs that you can have a look at and let's say you're looking for a rocket image now then I'm going to go over here to that search bar look for a rocket SVG now this one looks more or less like a rocket then let me zoom out a bit and there you go that's the image now what creates that image this code over here now we will get back to that code a little bit later however for now let's just download that SVG all right there it is then I go to powerbi now to use that SVG image we cannot just go to insert and then image because there we can only choose JPEG and PNG what we can do however is go to insert and then insert a blank button now for blank buttons somehow there we can use svgs now I'm going to put this one here in the middle then I'm going to go here to button style and here I would like to turn filling on and then here we can choose that SVG image that we just downloaded so over here SVG view output 2 that's the one and then we set the image fit to fit and the transparency to zero and there you go we have a rocket okay so that was option one we look for the SVG image we download it and then here in powerbi we can use a button to show it however now let's have a look at option number two which is much more interesting I'm going to go back to the website from before and instead of downloading that image over here I'm going to look at that code I'm going to copy theg code then I'm going to go back here to pobi and here I would like to add a new measure now let's call this measure rocket image okay so our image rocket and here I'm going to put in that SVG code in between quation marks just like this now H that doesn't work first of all we have a problem because there are these quotation marks also inside of the code so what we can do is just copy over the SVG code and go to a website like maybe jet GPT so jet. openen ey.com 3.5 that version is okay and I'm just going to ask it if it can replace the double quotes with single quotes Okay so replace this with the single quotes for this code and then I just paste in that code all right now it seems like it's done I'm going to copy that code then go back to powerbi and over here now that we have the single cords I'm going to replace this and over here oh one I forgot let me do that manually and that's it all right so we copied it over now there's one more thing that you need to do when you work with the SVG code in powerbi and that is here at the beginning we need to put in the following data colon image forward slash SVG plus XML column udf8 all right and then a comma space and then only the SVG starts okay so now that we have this where can we use it well we could use it for example in a table visual or a new card visual so I'm going to put this a little bit more to the left now I'm going to add a simple table visual all right going to put it right next to it and then I take over that image for a rocket going to drag it onto the table and well it just shows me the code not what I want what we still need to do is Select that measure then here under the measure tools data category there we can change it to an image URL and there we have a rocket again and you might think well that was a lot more effort for a simple image however option number two is way more interesting because with option two we can bind our data to the different elements of this visualization so we can say well the sum of sales should maybe determine well the size of that little bubble that we see there in the middle or it should deter the size of the wings of the rocket right so we can connect our data to the elements of the visualization and that is what makes option number two so interesting now for that however we need to understand a little bit more about svgs and the best way to learn about svgs is to build one yourself and then make it more complex as we go along and we can add things like gradients or we can add things like animations so let's do this together now over here I have a matrix visual for which we're going to add datab bars using SVG now if you don't know much about SVG I recommend just use a tool like jet GPT to get started and to figure out what is the code for different elements that you have in mind all right so let's use that then also as a starting point I'm going to open up a browser and make sure that you go to jet. openen ey.com and here we can ask it to create the SVG code for a bar chart and let's see what it comes up with all right now to quickly see how this code would look like I'm just going to copy it over to SVG viewer. Dev and over here I'm going to paste it in and there we have our bar chart well it's more a column chart so I'm going to ask it to flip it so flip it maybe it understands and then I'm going to copy it over again over here we can paste it in and oh actually worked it flipped the column chart to a bar chart now you see for each rectangle we have over here separate tag and also here for the text for the label we have a text tag all right so you always have a starting point ending point so when there is this forward slash and then arrow to the right that's where it adds okay good now the text I actually don't need so we can get of each one of these Tex labels and actually I only want to keep one rectangle so that it looks like this now that's going to be the starting point for what we want to have in powerbi so I'm going to well over here this is just a comment all right I'm going to copy this and before I copy it over to powerbi I'm just going to go back to um chat GPT again and then say replace double crosss with single crot four and then here the code now this is necessary because in the SVG code itself we also would have double quotes otherwise and that would make it a little bit problematic so I replace it with single quotes copy it and now I'm going to go back to powerbi okay now here again let's add a new measure and let's call this one SVG bar all right and here just like before we can do quotation marks then based in Das and here at the beginning we still need to add that beginning part so I'm just going to copy it in over there data image SVG XML utf8 all right good now that we have that let's see what happens when we add it to our Matrix visual so I'm going to give this a try I'm going to take the Matrix visual then I'm going to select here the SVG bar measure I'm going to select you for the data category image URL and once I have done done that I can add it to the Matrix and and yeah something shows maybe not exactly how I want it to be but something does already show so that's good now how can I make this look a little bit better let's have a closer look at the code that I just copied over you see we have SVG with SVG height now let's change these numbers a little bit and let's say we want to have a SVG width of 100 and a height of let's say 25 okay now here the X and Y position here it starts counting the well number of pixels from the top left corner so X and Y is down so here I'm going to put both at zero so X at zero and this one also at zero and here we have also a width of 100 and a height of 25 okay so this is basically the whole canvas and this is the size of the rectangle and now I basically gave them same size now that I have this let's go to the formatting options of the Matrix visual because here on the formatting options we still have to go to image size and choose the same dimensions otherwise you have way too much space right below it so the height I just want to have 25 and then the width I want it to be 100 okay perfect and now you see the bar is clearly visible now if you want you can play around with it and for example instead of the 100 wi for the bar we're going to go for let's say 75 and see how that impacts the SVG bars okay okay perfect so we have a connection there between that code and the image perfect now the next thing is is to bind it to our data and also make use of variables and you will see SVG code can get long so step by step we will get there let's add our first variable and this one I'm going to call SVG with and this one is going to be equal to 100 and then we're going to have SVG height which is going to be equal to 25 and then we can store the code for the image in well the image variable and then here at the end we still need to say return image now here inside of that SVG code we need to replace the 100 which we have over here with the SVG with so what you do is you select the number you do quotation mark quation Mark and ents in there to combine the two pieces and then in between we can write SVG with okay now that's the first place where we want to use that variable and then for the SVG height we can do exactly the same quotation mark quotation mark there and then % ENT and then in the middle SVG height and then for the color we can also use a variable so we can say VAR and then fill color is equal to and here you can just write blue or green you can also use Hax codes or you can use RGB and that's what I'm going to use so RGB and then I want to have 100 comma 120 comma 140 something like this and that I'm going to use then over here for where it now says blue all right so the same trck quotation mark quotation mark ent ent and then fill color in the middle all right now you see the color nicely changed from Blue to a grayish blue that you see now in the that Matrix visual all right so that is working however we did not bind our data to the SVG bars yet and that's going to be the next step so let's go back to our measure for the SVG bars and I'm going to create a little bit of space and what we need is to first of all figure out what is the range of sales so the sales maximum minimum then we see for each manufacturer that we have there in this visualization how it falls within that range all right now let's do this step by step so the first variable sales Min is equal to and then we can use a Min X function and here we can say all selected we want to iterate over the manufacturers and then we want to calculate the total sales for each manufacturer and then find the overall minimum okay and for the maximum we do exactly the same I select the whole row I'll shift arrow down copy that and then here we just have to change mint to Max and here also instead of a minx a Max X okay now that we have that well we can go for the most important step here we can determine the bar WID which is going to be equal to now here we want to divide and we want to take the total sales all right subtract that from the sales minimum okay that's the first part and then for the second part we want to have the entire range so so the sales Max minus salesman all right and that's it all right so this gives us the proportional amount total sales minus sales men that shows where the total sales for certain manufacturer is versus the overall minimum and then we express that as percentage of the entire range sales Max minus sales Min now to then convert this into well a certain width what we need to do still is multiply this to SVG with okay so that gives us the bar WID and that variable we can now use here for our SVG so instead of a WI a fixed wi of 75 we replace this with two rotation marks two M perents and then in the middle the bar WID and now let's see what happens to our SVG bars and nothing shows okay now let's see what the problem is I think I took the wrong measure so let me just replace this measure with sales total all right a different sales calculation in my model and that looks much better now we have data bars however what's still a little bit weird is that total bar there at the bottom so let's get rid of that I'm going to check if there is just one value for the manufacturer so selected value manufacturer okay and this one should well not be blank so for the total it would be blank because they are multiple manufacturers and then the selected value function would not return the value so not is blank all right then I want to return the image and otherwise a blank okay so that's by default the second argument of the IF function so we don't need to Define that that and that looks better now you for audio lock you don't see the bar because it's the smallest one so therefore what you could do is maybe wrap over here this still inside of round brackets and then plus 0.05 small adjustments and then we have also there a small bar for audio lock okay now these are normal data bars and this you could also actually get by just going here to sell elements and then applying that as conditional formatting for the sales total right so let me show you sell elements sales total data bars turn it on Boom you have exactly the same result so what's the point why am I doing all of this effort so that we have this now with SVG because well what you cannot do with normal data bars is add all different kinds of elements right this is just a starting point so let's continue the next thing that I want to do is add maybe a nice gradient to these bars all right something that's not possible with normal powerbi so that's let's give this a try now first of all I have no clue how to do it well then we could well Google it or just stick to a chat GPT so I'm going back over here and say could you make the rectangle um gradient well that looks like quite a bit more Cod now let's see what's going on here this is new that wasn't there before you see the definitions and there it defines a linear gradient and that's linear gradient is called bar gradient which is now used here for the fill and to refer to that defined linear gradient we have here the statement URL # bar gradient all right now let's copy the part from the DA's tag till the D's closing tag I'm going to copy it needs to be placed later on between the SVG opening tag and the closing tag right so keep that in mind I'm going to go back to powerbi and here we can add now a variable for the definitions right so here let's call it like that definitions equals going to paste it here and we just have to wrap it inside of quotation marks just like this now these definitions need to go where well it was in between the opening tag and the closing tag for SVG so that is over here now that means quotation mark quotation mark ent ent there we put the definitions okay now that was one part now every time when I make a change then what I do is well I'm going to check if I broke something no everything still works all good and the color is still the same because the fill color that it uses is this one over here but I want to have that linear gradient now to be able to refer to that linear gradient let's have a look again we need this fail statement URL and then then refer back to the ID bar gradient okay so I'm going to copy exactly that part and that's what I want to have over here for the fill color so I'm going to put that in between quotation marks and Tada we have a nice blue gradient now if you don't like the blue gradient of course you can make changes to it you have over here blue yeah line blue maybe we want to have orange instead so let me write the orange and let's see how that looks like and also not bad now maybe a little bit much for a real report but you get the idea you can just play around with all of these variables that you have there for that linear gradient okay now the next thing that you might want to add is maybe a little bit of Animation now let's give that a try as well I want want to have an animation where the bars nicely slide in with a fade all right now let's ask Chad GPT to make the WID of the bars grow from zero to well whatever the W should be so I'm going to copy over the code that we just wrote before and then here at the beginning I'm going to say add an animation effect make the bars grow from zero to well whatever the corresponding Val value is to the corresponding value okay now actually it's just one line that you see over there so animate attribute name with from 0 to 100 do one second and then freeze it okay so that looks kind of understandable I'm going to copy over that animation effect go back to powerbi just always double check do we have double quotes or single quotes single quotes all good and over here I'm going to add another variable so VAR and then this one is going to be for the animations and here we just paste it in between quotation marks so there we have the variable now we have to use it in our SVG code and we have to do it before the SVG closing tag so I'm going to create a little with space then we have again quotation mark quotation mark ent ent and then here we can refer back to the animations variable all right now let's see what shows nothing shows okay that's not so good so let's have a closer look now where did we go wrong now here you see we have the rectangle starts over here ends there then we have the animations let's compare this to what jet DBT wrote you see the difference here we have the rectangle starting then the animation and here an ending tag so it is not just on one line This animate needs to be inside of that rectangular tag so let's go back to powerbi and let's fix this so over here that closing forward slash I should get rid of then we have the animations and then we want to combine that with a closing tag for the rectangle so I'm going to add that here at the end so shift enter and then we have rectangle close and for the closing tag we need a forward slash over here and Tada we have our bars again and there was a small loading effect now to get that loading effect again I just make a small change so let's go back over here and then go to animate and I just instead of having it going to 100 or we need to link it again to are bar withd so bar withd okay now beay close attention I'm going to close the formula bar over here and now they're going to load ah beautiful all right so we have the animation we have the gradient what else do we need well maybe you want to have little Target lines on there no problem now then we just need to make sure that we bring in sales targets as Maybe extal lines now let's see how this can be done I'm going to go back over here to the measure now here we need to update the upper part not only do we need to find the sales manax we also need to do that for our sales Target right so that we have the sales Target Minimax and then we need to figure out okay the overall minimum and the overall Max and then update the bar with logic okay now to save a little bit of time let me just copy over what we need all right there you go we have the salesman we have the target men we have sales Max and Target Max okay so basically the same thing that we had before but then for also the target all right and then the two new variables that needed to be added is over here the arrangement which is just the minimum of the salesman and tment which one is the lower one and for the max exactly the same now you could choose to multiply by 1.1 which helps us a little bit later basically it just adds a bit of extra Banning on the right hand side later all right and once we have that overall minimum and maximum then we can use that for the updated Logic for the bar withd because now we want to compare the sales total to the overall minimum and the range is now defined by that overall Max versus the minimum okay good so basically everything stay the same it's just now we are also considering the targets as well now to the visualization it doesn't change that much but what are we doing this for well now the important part because we do not only want to have bars we also want to well add lines and here we can add another variable that's going to determine the line position of a vertical line that we want to place on top of the bars so inste of the bar wave I'm going to call this one line position now everything stays the same but what we need to update is the measure not sales total I want to have the sales Target so sales Target all right good now this is just a variable doesn't change the visualization on but how can we get a vertical line over the bars that we are currently looking at over here well we could ask chat gbt could you add vertical uh vertical line over the bar and everything stays the same except this part over here so you see we have now a line element so I'm going to copy that over to a measure and let's place it where we need to have it so that is after the rectangle now everything is still hardcoded in there so we need to update it so here you see we have the X position one where it starts where it ends so 50/50 and then the Y position goes from 20 to 50 and this is what we need to update because here that line the X position well now needs to be determined by this line position over here so that we're going to replace by our line position oh I forgot the ENT just like this and also here we have that 50 so over here we need to do the same then we have the Y position this is where it starts and ends now remember SVG Counts from the top down so the Y position goes from 0 to and this needs to be the height so here we need to have the SVG height so let's copy that over quotation mark quotation mark AB ENT and put the SVG High there in the middle now of course we could also create variables there for the stroke and for the stroke with so but for now we'll just leave it as it is black uh and a wave of two seems to be fine and you see now we have nice little lines popping up over there in the bar chart so perfect this work working however I don't like lines maybe I want to have circles now let's go back to chat GPT could you change the vertical lines to circles and there we have the circle element now I don't know why it added two we just need one so I'm going to copy over one then go back to our code and over here instead of the line I'm going to have a circle now for the time being I just leave the line in so that I can also copy over stuff you see we have here the circle X position y position so here for the 50 I would like to have again two amps in between two quotation marks line position and then over here for the Y I'm going to put that to zero radius three let's stick to that and let's see what it gives me this part I'm going to get rid of and yeah I do see circles but they are all the way at the top so probably want to adjust a little bit further and over here we have that y position so if the height is 25 we probably want to have the right in the middle so 12 now we could also let's just say SVG height so over here quotation mark quotation mark and then we could say SVG height ided by two just like this all right now let's see what that gives us but inside of lines we have now circles perfect so I hope that slow you get the idea of how powerful this is and how easy it actually is if we just ask chat gbt right so this you created completely from scratch and I hope that you slowly start to get a better understanding of how these SVG visualizations work because now you can basically build anything that you have in mind now you might say yeah but H these SVG visuals though they are not Interactive well that's true that's kind of the biggest downside of svgs so therefore usually you always find them in Matrix or table visualizations or card visualizations but there's actually a way to kind of take what we have over here and then use that also in a matrix and then make it look like a column chart or a bar chart and then it can interact with other visualizations on your report let me give you an example over here now here you see a very similar visualization as what we just built before however there's a big difference and that is well first of all it's not a bar but a column chart that's a small difference but the big difference is that this is added to a matrix visual in a different way because here if we go to the build panel you see we have here on columns month number and here on values values we have that SVG measure now this SVG measure let me open it up for you so that you can see it looks very similar to what we were doing just before all right so that is relatively easy so once you have this though here is added to values which means this is now creating for each month the bar all right so we have an SVG that returns a column sorry for each month and if we do it like this then this visualization can interact with other visualization so if I click you on one of the columns you see ah there's now an intera effect between that column chart and the Matrix and before we did not have that because we just added it basically as an image to a matrix visual all right so this is a second cool way in which you could use svgs but then making sure that the visual that you're building is interactive now the thing is though it's not perfect because if I would want to turn this into a line chart that would not be really possible because I would not be able to connect the different lines for from going from one month to the other and so you're limited to visualizations like com chart bar chart maybe just dots that you want to put in something like this okay and another thing that you want to keep in mind is that you the tool tab well the tool tab will not be pretty if you don't change it so what you can do is just turn it off so here turn it off under formatting options properties tool tabs if you don't change it looks like this you don't want to have that so build your own custom tool page or just turn it off okay so and now there's a nice little interaction Perfect all right so something to keep in mind let's go back to our main example because there's one big thing that we still need to talk about now these SVD visuals you often also use for showing trend lines right so your spark lines or spark line columns right that you then add to a matrix to quickly see the chance or to new card vision visuals it needs to be new card visuals because that's the only type of visual besides the table Matrix where you can use these svgs all right and there you still need to know one important trick okay now for that I'm going to well go back to an earlier version of a code and here we have that earlier version from before that returned These Bars okay now I would like to go for the simple version because the next thing that we're going to see is how can turn this first of all into columns and then show the development over time because then we need multiple columns for each manufacturer all right and that well is a little bit more tricky all right now we just need to flip it which means the bar width now needs to be a fixed number so this is going to be a fixed number let's say 10 and then this logic that we have here we're going to use for the bar height instead so over here we take the SVG height all right let me just C it from there and we multiply that by over here the proportional amount all right good now that will change how the visualization looks like of course now you see we have at the moment just one column which is not ideal now that column just shows on the right hand side now that is because over here if we go to the SVG there we have also the positioning right so the positioning the X position just want to copy this all right and put that to zero and then here for the Y this is where we need the logic however now this is going to be determined by the SVG height minus D and then here we have the bar height oh and I forgot the quotation marks over there all right now we have it popping up here on the left hand side but there's only one column and now we need to basically do the the calculation for every single month in the selected year now let's give this a try I'm going to go back over here to the Cod and here the first adjustment that we need to make is for the sales Min and Max so here we iterating over the manufacturers okay now we don't want to determine the Minimax based on the manufacturers but on the basis of the month year all right now why month here just because uh that's the period I want we could also do it for quarters years right so month year gives us later on 12 columns for that selected year so we have made this adjustment that now we are looking over the 12 months figure out what's the maximum and minimum all right so the range all right and now we need also two new variables we need to do a similar thing to well calculate proportional mes but not vertically but now also horizontally to see where the position is of each of the 12 Columns of the air okay so we're going to have date Min and Max so here we want to have the minimum and then dim date what do we have month year all right and here we repeat it for the date Max and then we have Max dim date month year okay so now we get to the most interesting part where we're going to have well the SVG codes for all of the rectangles for each single month all right so this is going to be a small table so table for the columns all right and we can use over here a function called add columns and then the table that's going to be well we can use the summarized function say dim date we want to have this for the month year and this will basically create a table with all of the month years and for each month year we're going to return that rectangle and I'm just going to copy it there from below and so over over here we can first give that new column a name let's call this one yeah it's custom that you say adds and then columns like this and then here we have the code from Below in between quotation marks so it's like this okay now we can close the add columns function now it doesn't find bar width and bar height because these two variables are below so I'm just going to put them a little bit up so that it doesn't return error all right now let's see what it returns well it just returns rectangles okay no nothing special yet because we are not done just yet here that table that basically returns over here that code for each month here still needs to be adjust it because well over here this is not enough we need to make further adjustments and here at the end we also need to make a few adjustments all right now let's do this so instead of that one rectangle we want to have now something new we want to use over here in concatenate X function we're going to iterate over table columns all right and then for each row we want to return the expression that's in at columns this one over there and to refer to it we can just use the square brackets around it cck close concatenate X just like this and we want to combine this with the closing tag for SVG and still we have rectangles showing so that's good nothing really broke and the only thing is that we still need to adjust this a little bit further okay so let's split this apart over multiple lines and make our adjustments so first of all we have here x which is now zero then here we have Y which is over there and then we have well all of the other stuff like the width the height the fill and that's it now for X for y and for the bar height we need to make them well proportional as we did over here so let me just do this quickly all right there you go here you see we have for the exposition similar calculations is what we used before for the bar height we have the current monia minus the overall minimum and that gets compared or divided by to the overall range which we then multiply by the SVG with okay so that's the first part then for the Y position exactly the same just something to keep in mind is that here you need to say SVG height minus that amount because we are counting from the top otherwise everything is reversed and then here for the uh with is just the bar width and the height and we have exactly what we had before all right now that gets created for every single month because here we use concatenate X we iterate basically over that table and for each table we return what's in the columns and what happens is well it looks like this we have now 12 columns popping up however it's not so visible and that's because we might still want to make an adjustment here for the width let's put this over here to 250 and then by 25 however now you see it also goes up a bit we just need to make sure that here the height is then also proportional so over here the WID 200 okay and then we make this a little bit wider okay the height if we put this one to 50 you see we have again similar problem so I leave it at 25 okay now count the number of columns 1 2 3 4 5 6 7 8 9 10 well that should be more so that has something to do with well how we made adjustments to the Whi so here we could say SVG with well there's one column that's all the way at the end so we need to subtract the bar with from it okay all right so now we have 11 uh there's one missing here all the way at the beginning which is the minimum and to fix that we can go back and just adjust the minimum with for example 0 point by multiplying by 0.9 so that that's smallest one still shows as a column here at the beginning and now we have 12 1 2 3 4 5 6 7 8 9 10 let to yeah all right now it works okay now this is nice and now we can of course figure out how to add all different kinds of effects we could also actually turn it into a line or anything else that you can think of now where is maybe even more common to add a visual like this is to a card visual now let me show you how to do that as well so I'm going to go over here look for a new card visual needs to be a new one and then here we can make it a little smaller and then on data I want to show let's say let's say it's the sales okay then we can go to formatting then here the call out value let's make sure that the values get displayed with without any units just like this okay and now I would like to also add an SVG visual like this one so I go here to cards then we turn image on and then here for the image we can change it to image URL okay and then click on the FX button and then here we can choose on The Matrix the one that we just built SVG bar step two now nothing shows at the moment why not H let's have a look well there's still one problem because if I go back you see at the end of the code it said if not as blank selected value manufacturer now that we need to get rid of okay so I'm going to remove that all right and that's better there we have our visualization now we just need to play around with the formatting right so we can go back and then here we can go and open cards image position maybe we want to have it at the bottom then we can make it a little bit bigger all right just like this and now it's just about making it pretty but the SVG visual is in there and that's the most important part okay so we went over all of the steps of yeah how to build your own SVG template but most of the times somebody else already did it for you so just Google it copy it over and then see how you can adjust it to make it work with your own data set but this at least gives you a good understanding of how to build one which then also makes it easier to adapt any template that you might find all right now I hope this was helpful if you have any questions or comments just put it in the comment section below if you want to Explore More videos then check out these two over here and I want to thank you for watching and see you in the next one

Channel: How to Power BI

Views: 54,642

Keywords: powerbi, datatraining, datatraining.io, bas dohmen, powerbi tutorial, svg, power bi svg, svgs, svg for dummies, dax, svg charts, svg visuals, visualization, powerbi visualization, visualisation, powerbi visualisation, custom charts, sparkline, gradient sparkline

Id: QOcP5OvSwlI

Length: 43min 3sec (2583 seconds)

Published: Wed Oct 25 2023