Create your own KPI SPARKLINE using DEFAULT VISUALS in Power BI // Beginners Guide to Power BI

in this video I want to show you how you can create a kpi sparkline card in power bi I'm going to show you how to create something like this where it shows you a lot of information like showing the total values showing the trends going up or down based on comparing against previous year previous month it shows you a card here which shows you the lines of the sales in different months it shows you and highlights the highest and lowest points of those months shows you a trend line which is fully customizable and dynamic based on the selections that you make in your report we're going to go through it step by step together so you can build it along with me as well all of that and more so without further Ado let's get started hi my name is fernan and welcome the resolutions abroad YouTube channel for recover tips tricks and best practices when working with power bi upload new videos every week so make sure you hit that subscribe button and the bell icon to get notified when a new one is out so power bi as you know already have some card visuals that you can use out of the box like the card visual or the kpi visual which gives you this kind of information and I've already covered both of these in video in the past so if you want to know more about how to use those go check those out so the solution that I'm showing you today is not a replacement for using those visuals because they also still have a place but more of an alternative if you wanted to give a bit more interactivity to your visuals so some of the limitations that you might have with the card for example is that you can only have a card visual there without being able to add trend lines or something like this the kpi visual also allows you to add trend lines but it doesn't let that you dig down into the details of those trends like which months are the highest which months are the lowest and also doesn't allow you to add and customize as much as you might like to do like changing the lines into an area or something like this so today I'm going to show you how you can do it quite easily in power bi using no custom visuals here I've already pre-built it but we're going to build it from scratch so you can see how I built it but the trick with this visual is that it's actually not a card visual but instead it's a simple line chart modified and formatted to make it look like it's a it's a card so there are a couple of different elements working here so we have the title and the subtitle which is a recent feature that was added in power bi that is dynamic now I've covered different ways that you can use Dynamic elements in your charts before so we'll use the same technique here the key things here are obviously the line charts that lets you hover over and and that's already a something that you don't need to build because that's already something that the line charts already do and also highlighting the highest and the lowest which is something that we'll have to do using some tricks that is not easily available or not so obvious maybe it's a glitch but we'll see so we're going to start with a an empty one here using the same data sets that I was showing you earlier so we're using our favorite data set here the Northwind date set which is a collection of different tables that for a fictional company that sells Goods internationally essentially we have a bunch of tables here that shows how many orders have been made those orders may contain different products how much they were sold for who they were sold for and the categories of the products so we've already created the data model because I don't want to talk too much about the data model itself because it's not the main focus of this demo it's just to simulate how it would look like in a kind of a real World scenario I've also pre-created a few things here like a calendar table which is something that we'll use for our time intelligence purposes I have just the dates column month and year just so that we can visualize the line charts and I've also got some pre-created measures here so the total sales which is pretty much just multiplying the unit price against the quantity and the total sales previous year which uses the previous year function to calculate what the total sales was on previous year based on the current context so let's start by bringing in and creating this line chart right so let's see how do you create the line chart so right here I'm still trying to get used to this on objects interaction in our total sales we'll drag that calculation in and then month is what we want to see here so it shows us the whole history of of our sales that we have in our data sets but we want to be able to filter by year as I showed you earlier so I'm going to add a slicer here let's see more options change it to a drop down uh sorry just a vertical list like this just a simple one and we're also going to add categories so to keep it simple I'm going to copy and paste and then I'm going to change the field to categories like this fantastic so now you should be able to select a year and see those sales change the categories to see by different categories by different product categories and that changes the line chart accordingly so before we continue formatting the line charts to make it look like a card let's start to set up the Dax Expressions that we'll use and we'll link up before we do that so let's start by creating a few measures here so let's start by creating the title which is basically just the total sales and then the actual value itself so that one is pretty simple so we're gonna just name this one title call it total sales and then concatenate it with just the value of the total cells so simple enough the next one is the subtitle which is is a little bit wordy we'll try to go through it line by line and it's the same subtitle that we used in a previous video it's just so that we can visualize something here so let's start by creating a few variables so let's create that variance minus total cells previous year and then let's calculate the percentage difference so it will be basically total sales and um total sales previous year like this minus one which will give us the percentage difference next is the sign which is what we want to show if it's plus or minus so if VAR is greater than zero we want to show a plus otherwise we want to show nothing let's also create the trend and in this case we'll just use the same calculation because I just wanted to show and see how we can add that Arrow so using the same logic I'm just going to copy that if the variable is like this let's change the plus into a an arrow so let's go to the symbols and under geometric let's just choose this one so that it just we're just saying basically that's going up now all of that is done if you just change it into something else so now let's combine this all together so sine 2 add a space there and then the sine which is the plus or minus and then we're going to add the percentage but we're going to wrap it with a format because we want to show it in percentage values so exactly the same as what we've done in the previous demo so it should be like this 0.0 percentage and then we'll add the pipe to have and give them a bit of space and then the sign again to show if it's plus or minus and then again we're going to show the variable but we're going to wrap it with the format to show it as if there's thousands we want to show like a little comma so zero comma like this and I think that should be it so we'll show that let me just show you how that looks like before we continue because that was a bit lengthy and I didn't want to spend too much time on that so here we go so this is what it outputs and as you change your categories so does that change so we're going to add it as a subtitle here shortly so the last bit is to create this logic to highlight the highest and the lowest now I've already covered it in a previous video so we'll use the same logic here go check out that video if you want to know more about it but like before I'm also just gonna kind of Blitz through it so that we don't spend too much time on it so I'm going to name it highlights and then we're gonna say variable first of all we're going to get a clean table I'm removing any filter context to it so I'm going to name it table I'm going to create summarize all selected from our order details table and group it by month and then the column will be called sales and then the value is total cells like this so that's the first variable now to calculate and find out what the highest is we're going to use Mac Max X use the table that we've just summarized and the calculation is the total sales we'll do the same thing for the lowest so Min X table photo sales like this now we'll create a switch in our return just to create this this logic we're going to say if the total sales is equals to highest we want to change the color of that marker to Green if the total sales is equals to the lowest value I want to show it as red otherwise so and anything else we want to show it as an alpha now I've not tested it to see if it works if you put anything else here but the alpha seems to work here so the the code for the alpha is hashtag 6fs and two zeros that is the logic so let's summarize here so what we're doing here so this measure is what we're using to conditionally format the markers to change the colors based on their actual values in a month and month context so if it's the highest month it will show or it will Mark as green if it's the lowest it will Mark as red otherwise it will be hidden so this code this hex code is Alpha which basically means like don't show so I think that's all of the Dax measures that we need to set up now we're ready to actually do the cool part which is to format our line chart here so let's go to our line chart here and let's make a few changes here so let's first go to more options let's remove our axes because we don't need them anymore remove the titles as well for the lines we want to make the stroke a little bit smaller because bear in mind that's going to be a small kpi card we also want to adjust the titles to be as small as possible you can make it a little bit bigger if you want but we're going to keep it small for now we're also going to enable the subtitle so that we can we can add that and make it dynamic as well I'm gonna change it into the same font here we are let's add the markers and let's add a few things here so I think I don't like the color of that so let me just change the color the default color into like that and let's change the titles to those Dax calculations that we've done so hit the FX icon here field value change that to the title oh I think I've missed something up there I think I wanted to change this into a currency so title so let's let's add that currency bit because I wanted to have the pound sign there as well so let's add pound like this here we go so it shows 87 000 pounds so now let's move on to the next bits which is the subtitle FX field value based on the subtitle here if you hit OK so you can see that now you have that value that changes as you make changes so does that value and I think I did I wanted to have something else there to show so let's go back to the subtitle and I wanted to show an arrow going down if the value versus previous month is going down so I'm going to open up the Emoji board here it's Windows dot if you don't know yet it's the symbol then we'll simply just choose the down arrow now if you hit enter you'll see that that down arrow is there now in our kpi card and choose up Arrow if it's the values going up compared to previous year the next thing is to update the actual markers to show the highest or lowest now what you'll notice if we go to the markers and colors you'll see that there's no FX icon here which is a little bit concerning but there is a trick to get around this limitation so if you go to your build your visual change it into a bar charts temporarily now you will have the ability to change the colors of your columns here so if you hit FX here and change the color based on the calculation that we've created so the highlights you'll see that it only highlights those months that are the highest or lowest so now if you go and switch back to the line charts there you go you'll see that within this kpi card it kept those colors even though it's not available in the markers option in the line chart so that's a bit of a hack if you didn't know yet and the only other thing is to remove and hide all of these other markers because we're not interested in them so to do that you just simply change the size and you'll see that if you change it all the way to zero okay it won't let you but there you go so it will let you see all of those lines while keeping the highest and lowest colored I think we can make the lines I think the lines are maybe a bit too thin here we go so that's it and obviously the last thing is just to make it look pretty so I like to add a few things here under visual border let's add a colored border here so a great border with rounded corners and there you have it so you now have a kpi card that allows you to give that gives you tool tips gives you comparisons against previous months or previous years or your targets your budgets and is fully Dynamic so as you make selections within your slicers it not only changes the line charts but it recalculates and highlights which months are the highest and the lowest that's and that's really it for this video I hope you now know how easy it is to create this kind of sparkline charts in power bi thanks for watching as usual give this video a like if you found it useful give it a dislike if you didn't send it to do better for next time ask your questions in the comments section box below so I can help you and you can help others if you really like this video we have a patreon page where you can support the channel and get exclusive perks like Early Access demo files and credits at the end of these videos thanks again for watching and see you in the next one bye

Channel: Solutions Abroad

Views: 5,183

Keywords: solutions abroad, power bi, powerbi, power bi tutorials, power bi for beginners, beginners guide to power bi, data analytics, dax, data modelling, data visualisation, business intelligence, how to power bi, power bi how to, power bi best practices, power bi tips and tricks, power bi standards, power bi patterns, power bi help, power bi tips, power bi 2023, power bi kpi, power bi sparkline, power bi card, power bi trend, power bi variance, power bi kpi card

Id: EJB__tBsDNE

Length: 16min 32sec (992 seconds)

Published: Mon Jun 19 2023