A CARD VISUAL you haven’t SEEN before | MULTIPLE KPIs with line breaks ON HOVER in Power BI

i wanted to take my kpi cards to the next level by adding a hover effect so that i could show more information about the kpi for example the forecasted value and the difference to the forecast something like this over here now the difficult part was not to set up the hover effect what appeared to be much more difficult is to get the kpis on separate lines so basically to get the line breaks in now in this video i'm going to share with you how to set it up [Applause] welcome to how to power bi my name is boss and if this is the very first time for you visiting this channel then make sure to hit that subscribe button if you want to stay up to date on all of my videos in which i share everything i know about power bi let's get started now as a starting point i'm going to insert a normal card so let's click on the card visual and place your measure onto the visual then i go here to format general and then i set the width to let's say 250 and the height to 125. now let's then also add a borderline so i'm going to turn the border on and then here on the data label let's the text size a little bit down so let's say that we are happy with a text size of 28 and then here for the category label i'm gonna make that also a little bit smaller let's put that to 11. so this gives us a basic kpi card now the next thing that we want to do is show some different text when we hover over the card because at the moment when we hover over it then you will see it just returns the value of the measure now to do this we need a blank button first because with buttons we can determine the formatting for different states the default state and also the unhovered state and we're going to overlap that blank button with the card visual all right so let's go to insert and then buttons and then we choose a blank button now here we can go to general and put the width just a little bit below the width of the card and then for the height also here just a little bit below what you chose for the card and then we can overlap that with the guard visual and nicely put it in the middle the next thing that we want to do for the blank button is to turn off the outline and turn off the background color and then we can go here to text and turn that one on then only for the on hover state we're going to add some text so let's say this is some example text all right now this will only show when you hover over the button now at the moment you see then text of the button but behind it is still the card visual so we want to change the filling when we hover over the button so if we add some filling and say for the default stage the transparency is 100 then here on the on hover state you put the transparency to zero so now when you hover over the card you see the text of the button and there's a white background of the button so therefore you don't see the text of the card and when you take your mouse off the card then now you see the measure value that you placed on the initial card visual all right so that is working however the tricky part is when we want to add multiple kpis on separate lines now let me show you what i mean let's create a measure and for this measure i'm just going to put in some example text so let's call it example and then over here we're going to have this is some example text one all right and we are going to combine that with some other text strings so over here we are going to go to the next line shift enter so over here we have text two and we're going to combine that with a third line so text three now i select my button again then go to text and then on hover and instead of hardcoding this text we are going to use some conditional formatting now the field value is going to be set by well that example text that measure that we just created so now you see the example text on the button when we hover over it however the problem is that it doesn't nicely break it down into separate lines so this is some example text one and then this is some example text two well the second part should go on the second line and that is not what happens and just continues now to achieve that it's quite tricky now let's explain how it normally works when you want to add a line break and for that let's insert a new card again now on this card we're going to take our example text measure let's put it on there let's go to data label and then here put the text size a little bit down let's say to 16 and then over here we don't want to have the category label i'm going to turn that off now when you play around with the size of the card then you see that it just keeps on going on a certain line until it reaches the end and then breaks it to the next line okay now this is because word wrap is on here on the formatting options you can also turn it off but then you just have one line and when it reaches the end well you just have dot dot when it's too long now to go to a next line we have to make an adjustment to the measure we have to add a line break so if you go back to your example measure and now we have two ways in which we can insert a line break either you go over here and you use the unicar function unicar and then done and unicard done basically goes to the next line adds a line break and we just put that at the end of each text string okay now let's see if this works now nothing really changed but that is because over here word wrap needs to be on and now it respects that line break that we have in a magic now another way to achieve the same thing basically would be as follows you add a new line in between the quotation marks okay so that that ending quotation mark is on the next line and you do that here as well okay and now you see when word wrap is on it still goes to the next line as well without using that unicar function okay so basically these are the two ways now what is important is that word wrap needs to be turned on because if i turn this off then you see we have the same problem as before because it basically gets rid of all of the line breaks and spaces etc now the next thing that is important to know is how power bi deals with spaces okay so if we go back to the measure and we place five spaces between text and the numbers then you see by default there's no problem it shows the spaces however when we go here to data label there's source spacing which you can turn off and then it gets rid of the redundant spaces i saw the number of spaces that are beyond one now the options for word wrap and source spacing is not always available for each element in power bi and that's exactly where the problem is because if we go back to a button and we hover over it well the problem is still there even though we have in our example measure well the line breaks in there and some spaces and if you hover over the button well spaces the redundant spaces are gone and there are no line breaks so now you might think well we have seen how to fix it you could just take the button go to text and play around with word wrap source pacing however these options are not there so how can we get it onto separate lines then well that's what i'm going to show you right now now the basic idea is that we need a character that looks like space but doesn't get stripped out by power bi for the button text now one character that works is if you go to the website emptycharacter.com and then you can over here just copy the blank space okay and then go back to power bi take your example measure and instead of these normal spaces we're going to put in these characters that look like spaces but are not really okay and now when we hover over the cards now you see those spaces don't get deleted so let's now instead of having here some example text go for what i actually wanted to create and which is a little bit more insight on the kpi that we are looking at sales actuals year to date i want to have the real value the forecast and the difference to the forecast okay so let's remove over here the example text and rename it so this is going to be my button text on over now we want to have one line for each kpi and each kpi i'm going to put in a separate variable so we're going to have var kpi1 text is going to be equal to and then you first have the header so you could say sales ytd and then call in space and after that the actual value now so here we can just refer to the measure so we have sales actuals year to date and then we repeat this for the next line as well so over here we have kpi2 text and this is going to be equal to the forecast value also here gone in space and then the value now so over here we want to have the forecast year-to-date all right then the third one that is going to be kpi three text and this is going to have the difference and over here you also have a measure that's called sales versus forecast all right and what we want to return well we're going to have a separate variable for that as well so we are going to have a variable called summary text and this is going to be equal to well kpi 1 and kpi 2 and kpi 3. all right now let's return then the summary text so now when we hover over it well that looks kind of ugly because the numbers need to be formatted so let's go back to the measure now to fix the formatting we need to use the format function for our values that we are returning inside of our text string so if you go back to the measure and then go to the first measure reference then you use the format function and then as a second argument you can use the custom formatting string if you don't know how these custom formatting strings work then check out this video over here where i walk you through all of the details now for now just type in a quotation mark then the formatting string so here you can type in a zero comma comma and then m and then closing quotation mark and then close the formatting function now let's just double check if that works for the first value all right that looks good then you go back and repeat the same thing for all of the other ones so let's copy down and then also here we're going to use the same formatting string and now this already looks a little bit cleaner but the next problem that we have is that we need to have it on separate lines and the line breaks the traditional line breaks with the unicard 10 or using shift enter to go on the next line within the quotation marks doesn't work here now the work around to go to the next line is to use that weird empty space that doesn't get deleted by power bi so with that we can just put in as many spaces as we need to push it to the next line all right now we can do that inside of the magnet so let's go back now for imagine let's first create another variable and let's call this one space and in between quotation marks we need that empty character that we can copy over from emptycharacter.com just copy it from here go back to power bi and put it in between the quotation marks then the next thing that we need to decide on is the card with all right now let's randomly put it to 30. okay now this number we might have to change later on depending on how wide you made the card now the next thing that we need to do is calculate the number of spaces that we need so we're going to put that into two separate variables okay so let's call the first one kpi 1 and then number spaces now to calculate the number of spaces we can take the card with and subtract over here the length of the kpi1 text okay and then we can repeat this on the next line so ctrl i to select it then with alt and arrow down or alt shift or down you can copy down all right so here we can then have kpi number two and here i also want to have kpi to tax length so now we need to adjust the summary text now let's indent this and put it on two separate lines okay so we have kpi one two three and after that we need the spaces now here we can use the wrapped function which repeats a certain character a certain number of times now the character that we want to repeat is the space how many times well by the kpi in one number of spaces all right and we repeat the same thing for the next one okay so i'll see here wrapped but then kpi 2 number of spaces now also here in the end we need an ampersand okay and what we want to return is the summary text so now when we hover over the card you see it is on separate lines however the number of spaces is not correct yet now to fix that select it and first of all you want to double check that here for the text you have the alignment set to the left hand side so over here you see now it already looks good however i was lucky because i knew that i needed to have 30 spaces okay usually you have to go back to your measure and then play around with the card with so now when we hover over the card you see the result that we were looking for we have three kpis it over three separate lines it only shows when we hover over the card okay but the only thing that i don't like yet is that the numbers are not nicely aligned so maybe it would be nice to add a little bit of space to the left hand side and then a little bit spaced on the right hand side so that they're nicely aligned would make it better to eat okay now the idea would be the same so i already did it for you and to save a little bit of time let's just go over the end result okay so i'm gonna go back here to my measure and i'm going to replace this with the final measure that i wrote now here i have the same variables as before space and card width but i introduced a new one max tax length then i want to have for the well the is off the kpis okay so i put that to 15. then i divided up the text strings into two parts we have the text and we have the values okay so here we have the text and then here we have the values all right and then we have to calculate the number of spaces now what i did is first i calculated the spaces on the left hand side so that is basically that 15 from before that i hardcoded there and then i use that to see okay how many spaces we need to fill it up until 15. okay this assumes that the maxed and then the max length of the headrest that i have is 15 okay now here the spaces on the right hand side we can calculate by taking the card width and then subtracting the length of well the kpi one and two text okay and then here we just put that together for the summary text now when i hover over my card you see it doesn't work straight away now that is because i still need to take the card go to text and over here we need to have a font that is monospaced because now the width of each character needs to be the same otherwise this is not going to work and one monospace font would be for example consoles and then i put the text size a little bit up and you see when i hover over now it does work okay and it depends a little bit on the font how big the font is how many spaces you need but that is just an adjustment of the same measure so this is how you can take your kpi cards to the next level by adding this hover effect and showing more insights for that specific api now of course i also wish that this was easier and it will be much easier when they add this word wrap and source spacing options for the button so maybe in a future update you don't need this anymore now thank you for watching and if you have any questions then put them in the comment section below if you liked the video and got some value out of it then consider subscribing and i hope to see you in the next video

Channel: How to Power BI

Views: 97,574

Keywords: hover effect in power bi, KPI card power bi, power bi KPI card, multi KPI power bi, power bi multi KPI, multi KPI card power bi, power bi multiple KPI card, create own KPI card, on hover KPI card, power bi, power bi tutorials for beginners, power bi desktop, how to power bi, datatraining, howtopowerbi, bas dohmen, datatraining.io, bas

Id: LviQRWazh8U

Length: 16min 36sec (996 seconds)

Published: Wed Oct 20 2021