Power BI: Build a custom KPI scorecard

yo what's up this is Patrick from Guyana Q and in this video I'm going to show you how to create your own custom score car with power bi stay tuned [Music] okay custom scorecard why Patrick why there's so many custom visuals out there that allows you to build your own custom scorecard your own not custom but your own scorecard in power bi well I was working with someone and they want to have more control over it and they wanted to set their own thresholds kind of in the DAX and not write too much Dax I was so many things right and they want to have their own symbols oh okay right they didn't want to use the little symbols that were built it was like okay okay pause pause pause anyway think I can do this and so that's what this video is about so instead of all this talking you guys know what I like to do is head over to my laptop alright so let's pretend they have my penis it's real so they have a matrix and basically in the matrix they had a bunch of numbers okay and so the person that was consuming the reports like I don't want to look at all these numbers can't just give me like an up arrow a down arrow and a sideways arrow to let me know if we're trending up if we're turning out of we're just not doing too much okay all right and so you can see in this table this matrix is actually a matrix if I enable drill they really they really want to look at this on a month level okay by year so it was built just like this I was like okay so I started thinking about it and there's a function inside of power bi called you know car Unicare however you want to pronounce it that you can use you pass it a number and it will actually return the symbol okay and so we'll get to that in just a little bit alright so the first thing you want to do when you start building this out is you need a value so I've created a couple of measures here and we'll walk through each one and so the first one is my KPI value and this little piece of Dax all it is is a year-over-year calculation KPI value is what you're gonna use to compare to your threshold so you know maybe this is just sales maybe this is some type of retention or something like that whatever it is right in my case is just sales all right so I wrote this little backed right here to do it and then the next thing you want to do and this one is really important it's your status okay so the status is kind of like if I'm greater than my threshold I want to return this if I'm less than my threshold and so let me show you so if you go to the KPI status what you'll see is I set my bottom threshold at negative 5% in my top threshold at 5% and so and then I'm using the switch function to say if my KPI value if my year over year percentage is less than 5% returning negative 1 if it's greater than 5% returning 1 and if it's between those two values return a zero okay and so it's easy for me to manage easier for me to manage this then actually managing those percentages and if I need to change those percentages I just change it in one place and watch watch I'm going to show you this everything else just inherently picks it up right watch pay attention ok so after I get that done then I went out and I try to look for a webpage that had all these different values that return these unique are these symbols and I needed the numbers for it and so what you guys will see in our git repo is you'll have a foul call ASCII I mean I just need to change the name but anyway well we'll get that sorted out in a little bit but what it does is it returns the value for these corresponding symbols you just go look up and the ones I use were the up arrow the down arrow and the both ways arrow there and I use those values to display those symbols and then I use the function that I talked about earlier alright to actually return them so I created another measure called KPI indicator let's take a look at it right and in this measure what you'll see is so I'm just creating a couple of variables to tuck those you know those symbols in up down in my both way symbol and then I use my KPI status and I say hey if the KPI status is negative 1 turn the down arrow if it's 1 return the up arrow if it's 0 return the boat ways arrow now remember I said this is that KPI status is the foundation right it's everything brings all this stuff together otherwise I'd have to tack those percentages all about and all these measures when I know negative one is red one is green and zero is yellow I just use that over and over and over it's easy to remember instead of trying to remember those thresholds right let me show you something else about that also and then what I did I use almost the exact same logic for the color and so you can go here and you can see I declared red you know this is the color for red this is one group yellow and this is one for green and they use the exact same thing right okay guys that is negative one red bla bla bla bla bla exact same stuff you were able to download a copy of this PP IX file so you have to memorize all this right okay so I did all that and it was super duper excited I was like okay now now I'm gonna use this but before I did it I had a thought I was like well they really need the symbols couldn't I just color up the numbers and now you have the number and the simpler the number and the color so what I did the very first thing I did I went back to that matrix and then I clicked on my open up the visualization pane I clicked on the format' icon and I went to conditional formatting right and then I said font color advanced controls I change it from color scale to feel value and I use my KPI color right KPI color click OK BAM everything's colorized yellow green we head all over the place right and I showed it to them they were like no no you silly little boy Patrick this is cute this is cute but we want the symbol so I can quickly see if something is going up right for a particular month and country it's going down I just want to look at it if I want the value we'll talk about that later okay so what I did was let's turn the front color off and then we're just gonna copy this this matrix right and then I'm gonna delete go back to my fields here and delete the KPI value and drag in my KPI indicator so now I can see my arrows going up and down in all these different places and they'll correspond to my thresholds right and now I just need the color and all I need to do now I always use my color value so you can do this two ways I'm gonna show you both ways okay both ways if you don't want to write the extra measure for the colors which I suggest you do because you can quickly and go change the colors if somebody doesn't like them that way but what you could do is the first way to do this it's really simple you turn on your foreign colors alright go to make sure you leave color scale selective internet sales KPI and then you choose the KPI status and then diverging because you want red yellow right and then change all three of these to number okay and then you say negative one red cuz that's my status right zero is yellow one is green I click okay the color is everything up you saw the steps right you saw all the clicks I had to do should be do it the other way which is really easy so we're gonna go to advanced control from color scale to fill value and simply choose my KPI color and bam colors everything up for us without remembering the statuses cuz all the statuses are where in my Dax and now look at this right look at this this is great so now I know if it's trending down trending up not doing anything based on my thresholds but watch this watch this I want to show you something let's say let's say they go Patrick I'm looking at these numbers and negative four point two six is not that should be rated and so if I look at my matrix for 2017 it's actually yellow right and I want it to be trending now well what would you do how would you change it what's really easy you go to exactly one place go to your status right and I say okay so you want to be three percent yeah so I click update I change that now look now look at this right it's going down and it corresponds and I change it in one place it changes the color and it changes the icon that I'm displaying this is phenomenal alright I'm gonna change it back because I know what I want those thresholds to be right and then they I have one final request say Patrick it would be great if I hover over this not to see that little down arrow up arrow sideways there I want to see the value well that's easy right you create a report tool tip and if you don't know how to create a report to tip I did a video back in that I don't know we didn't wanna go take a look click on the format' make sure you click that element create your report tooltip scroll all the way to the bottom turn the two tip on change it to choose report page change it to KPI details now right we'll delete this we don't want that anymore never bring promote this guy up drag it out now if I hover I get these values to see my nice little report tooltip is falling within the thresholds I turn on drill go to 2019 right now I can see this for each one of these this is oh this is just so perfect you guys are doing such a great job over at the desktop team all right what do you guys think yeah questions comments are you doing this have you done this before tell me post it in the comments below is your first time visiting guy in the cube channel hit that subscribe button if you like my video a thumbs up as always from Adam and myself thanks for watching or see you in the next video

Channel: Guy in a Cube

Views: 74,478

Rating: 4.9701118 out of 5

Keywords: power bi, power bi conditional formatting, power bi dax, power bi demo, power bi desktop, power bi desktop tutorial, power bi desktop tutorial for beginners, power bi reports, power bi scorecard, power bi scorecard conditional formatting, power bi training, power bi tutorial, power bi tutorial for beginners, power bi videos, kpi power bi, bi, conditional formatting, business intelligence

Id: 9eKLdujI9_A

Length: 9min 33sec (573 seconds)

Published: Wed May 08 2019