NEXT LEVEL KPI CARD using NO CUSTOM VISUALS // Power BI Tips and Tricks in 2023

in this video I want to show you how you can create a dynamic card like this using the built-in card visual in power bi that allows you to see values variances percentage and total and allows you to change those values dynamically using slicers as well as changing the comparisons to different values like comparing against your budgets or targets and other Dynamic formats like adding pluses and minuses to your values as well as colors all of that and more so without further Ado let's get started hi my name is fernan and welcome to the solutions abroad YouTube channel or recovery tips tricks and best practices when working with power bi I 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 the card visual is one of the basic visuals that you'd be introduced with when you start working with power bi and I've covered it extensively in the past showing you how you can add different things like Dynamic colors and subtitles to it to add some different ways to visualize these cards now the problem with those Solutions is that a it means that I needed to create multiple visuals on top of each other which means that if you wanted to pin it in the dashboard or move it around it might not be as convenient as just having one visual the second thing is that it's fairly static so typically you would show it in a card to compare against previous year for example but you won't be able to change that comparison to other things like you might want to see how your numbers compared to last year or maybe against your budget or your targets and Gustav the deck from LinkedIn has created this super cool solution that just utilizes the subtitle feature in RBI to create a lot of the dynamic elements and make it make it more Dynamic so I really wanted to give it a try to see if I can do it for myself and I think I've figured it out so I want to show you how to do it step by step so here is the reports that I prepared for today there's just a few things here and this is where the Baseline would be so we have a fake table here with the sales table with some different values like your total sales your budgets targets and what the values are for previous month and same period last year and these are the values that we want to compare against and the value that we want to show is the sum of sales or the pre-created all of the measures here um so you don't because that's not really the point of this demo and let's say we got get the task of wanting to show the sum of sales and the comparisons against any of these values so what we're going to do is we're going to add a card visual and we're just gonna add the total sales which is the typical roots that you would take the problem with this is well actually you can do it like this too but what's good about the card Visual and the latest power bi versions is that you can add more Dynamic elements to this more than what you could before so what do I mean by that so if we click the more options here to show the format pane you can see that in the call out value you can add the dynamic values obviously based on the measure that you add there you can change the colors individually using measures and under title you can add on top of already the call out label here the total sales you can add Dynamic titles as well so this is a title so you'll see it's up there and on top of that there's also another one here subtitle which again also allows you to add conditionally formatted measures and calculations for yourself so this adds a lot of ways for you to add Dynamic elements within this same card which is what we will use and utilize in a second so this is a subtitle I'm gonna just format them to what we need so I'm just going to put them in the middle here maybe make the call outs to be slightly smaller probably just have them in the same font as well so we'll make it through 15. category we'll just change all of them to the same font this all right so here is now the basic card that we want to work with now from Gustav's solution there are a few things that I really liked and I just wanted to see how you would work your calculations to each of those elements so the first bit is the title at the top which is going to be pretty simple but you can create it dynamically if you wanted to so you can just say total sales here now so that will be the title of your card pretty simple we'll make it slightly smaller we can now add the value of what we want the total sales because what we want to do is we want to have the the title of the card at the top we want the value instead of being in the call out value we want it to be in the subtitle we want the variance the percentage and you know the plus or minus here in the call out value instead and then in the label we want to show what we're comparing against so let's start by updating the the subtitle so what we want to do and what you'll notice typically this is probably what you would do is you'd go to subtitle go to FX and just say oh I just want to pick the total sales but it won't let you and that's because in subtitles you can only put text values so it could be text column or text measure but it won't let you choose any measures or columns that are numerical so it needs to be text formatted so what we're going to do is we're first going to create this text formatted total cells so kpi total sales and we're gonna say total sales like this and then we're going to wrap it with the format string like this and then the format string we're just gonna fictionally say that our total sales is in British Pounds like this zero sorry you should be like this zero and that should be it so that's the formatted total sales but to convert this into a string we're just going to add an empty value like this so now you have a kpi total sales that shows you the total sales in pounds formatted but in a text format it sounds a bit confusing but basically just allows you to put it in a subtitle we're going to go back to our subtitle format here under the FX icon we'll select now our new kpi total sales if you hit OK there we go so we have the number right there for us I'm just going to make this value a little bit more prominent because we actually want this one to be the value instead of the collab value I'm just going to make it prominent like this and we're going to create a bit of a separation between the top values and then the bottom values here so I'm going to create let's see a background create a light background here and we'll make these call out slightly smaller just a tiny bit smaller like this and the callout value as well to be a little bit smaller and we're going to update this in a second as well and let's do a bit more styling here what else can we do so we can remove the or we can add the visual border make it as gray and add some rounded Corners to make it look a little bit nicer and there we go so that's pretty much done the next thing that we want to do from here is to create and calculate the variance between our total cells against what we want to compare against so to keep it simple we'll start to compare with budget first and then I'm going to show you how you can switch those calculations to other ones later so what I'm going to do is I'm going to create a new measure right here we're going to name this one well we'll just name it total sales API total sales versus budget so that's what we're going to compare against for now so we're going to start by creating a few variables that we might need so we're gonna create VAR here and then we're gonna calculate the total cells minus the budget so we putting it in a variable so we can understand later what we want to add in this variance plus or minus next we're going to add a percentage which is basically just com like calculate the variance in percentage so we're going to add a divide total sales against the budget like this and then minus one so that will create the plus or minus percentage next we're going to create a logic for the sign the symbol at the front which we're gonna do a simple if statement to say if the variable is greater than zero then do a plus else don't do anything so just leave it as whatever it is because if it's negative it will just show negative anyway now that we've done that we just do a simple return and then we're going to create this concatenation so the first thing that we want to do is show this the percentage difference so sine and then we're going to add the percentage but the percentage we need to wrap with a format because we want to make sure that it's showing in percentage with however many decimals that you might want so we're gonna say like this 0.0 percentage and then we're going to add this separator so we want this I like this and then we want to then do another sign just do like this and then variable but we're going to just start with format then so we want variance and then same thing so we want this to be formatted in the way that we want so it'll be like this and it should be it so now what I'm going to show you so kpi total versus budget so maybe yeah so let's just leave it as it is actually and let's now go back to our card visual here change on the build change the field so instead of showing total sales because we're already showing it in the subtitle we'll switch that into something else so we'll choose the one that we've just done so here as you can see it shows us the total cells 44 000 pounds is saying that against the budget which the budget for whatever we've currently selected is 99 000 that's 55 percent lower it shows you the percentage as well as the value the variance value and this is exactly where we want to be at now typically what you would do probably a logical way to do it as well is to create a um a disconnected table to be able to switch but the problem I've encountered some problems doing that so instead I have decided to use field parameters instead even though it's a little bit weird to do because it means that we have to weak create these measures at least it will allow us to switch what the calculation is below our card here so what I'm going to do is I'm going to copy this measure that we've just created create a new measure I'm going to create total sales versus Target and then we'll change all of the references to budget to Target instead because everything else will stay the same versus last month and then the here we're going to use previous month which is the measure for getting whatever it was last month and then last one versus last year which I believe the measure is called same period last year like this okay so now we have these four different measures that compares to total sales against whatever the value that we want to compare against and now we need to find a way to swap them around so this is where the field parameters will come in handy so I believe it's under modeling new parameter Fields parameter we'll select all of those kpi calculations that we've just done so budget Target last month last year I'm just gonna rename them to the name of these parameters here in our field parameter needs to be the name that you want to see at the very bottom of our card here because that's what will update that dynamically so we're going to change this into versus last month I will just name this one comparisons all right so now we can select from our slicer here what we want to compare again so let's just compare it by budget first so if we select this card now and under build we'll change the field instead of using our measure to show what the value is in our comparisons so it hasn't changed yet see what happens when I change the comparisons so now it's comparing against the targets against last month and against last year and that also changes depending on what dates you pick in the date slicer here which is is pretty cool now the last thing that is a little bit complicated um is to add colors so colors is a good way to signify if the numbers that you're looking at is good or bad so for example you might want to show if it's a negative value like this you want it to show as red if it's a positive you want to show as green I mean it's a pretty good indicator pretty quick way to to see if your values are doing good or bad now there isn't an easy way to do this apart from just creating your logic within the measure but if you follow me it should be you should be able to do it as well so we're going to create a new measure here we're going to just name it kpi color we're gonna create a few variables here so first we're going to create a selected value so whatever is selected in our comparisons table so um it's just so that we can refer back to it later and then we're going to create the variable to um look and see what is currently selected in this field parameter so I'm gonna put true here and then we're gonna say if the selected but the output of it is like Fields it is a bit weird so what I'm going to do is I'm just going to see what is in this in this field so we're going to use contain string to say if this thing contains Target somewhere then it needs to show or it needs to compare total sales minus Target like this so this value that it returns will be what will Define if the color is positive or negative if it's green or red so we're gonna do a few of these statements so we're going to say if it contains a budget then we want to compare total sales against budgets and if it contains last month then we want to use previous month oops oh yeah thank you very much and then the last one since there's only one we're just gonna do total sales minus same period last year so it's aired out because we're not done yet I just accidentally hit enter now we're gonna do a return statement so the return statement is another switch statement unfortunately it's a bit dirty but it works so we're gonna do if the value is greater than zero then we want the color to be green if now the variance is less than zero then we want it to be red if not if it's zero it will just be black like this so that's the kpi colors done and as you can see it's Dynamic based on what the comparisons is currently selected so um if I just show it to you here in a card you will see what happens so if I make a selection you'll see that it changes based on my comparisons and it gives me the correct color so all we need to do is assign that color to a value here in our card so in Gustav's demo I just put it in the the callout value here at the bottom so that's what we'll try to do but you can add it anywhere you can add it even at the on the actual value in our subtitle so we're gonna go to where is it so callout value is the bottom part so this is where we want to add the colors so we're going to click the conditional formatting here we'll choose it based on the field value and we'll choose kpi color if you now hit OK let's just leave this and there you go so you're all set up so you now have the ability to go to different months like this and then the ability to also switch what you're comparing against showing you the variance in percentage against the whatever the value is as well as showing you colors showing green or red and that's really it for this video I hope you're now a little bit more familiar with how you can squeeze the card visual to its full potential using a few techniques along with the subtitles if you want to learn more about Gustav dudex technique I'll leave a link to his original post on LinkedIn and I think he deserves to follow because he has a lot of sort of data visual techniques that I mean they're quite unique for me and I'm learning just following his posts so give him a follow because I think you will learn a lot from him thanks for watching as usual give this video a like if you found it useful give it a dislike if you didn't so that to do better for next time ask your questions in the comment section box below so I can help you and you can help others if you really liked 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: 12,776

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 kpi card, power bi kpi variance, power bi kpis, kpi, kpis, power bi vs budget, vs last month

Id: UOnkB1ExYp0

Length: 18min 47sec (1127 seconds)

Published: Wed Jun 07 2023