I'm going to show you 3 Power BI visuals that I bet you have never built before. Now watch them all and let me know in the comment section below if I was right. Let's start with visual #1 a column chart that turns into a line chart. You know that situation where you have a column chart that just looks perfect, but then you choose a bigger time period and you end up with too many columns? Well then you would be better off with a line chart. But you could also have the opposite problem. You choose a very small time frame and then, well, that line chart doesn't look right and you would be better off with a column chart. So what is the solution? Well a column chart that turns into a line chart when you go over a certain number of months. So here you see in my date slicer. I have a period selected of 12 months. But as soon as I go over the 12 months then my columns turn into a line chart. Now let me show you how it works. It all starts with a combo chart. So the combination of a line and a stacked column chart. Now then we need two measures one for the column series and one for the line series. Now what we need to do first is to calculate the inputs for our condition. Not in our case, we need to know the minimum date of the selected period, the maximum date of the selected period and we need to calculate how many months that actually is. And that all happens over here in these first three variables and then we can build our IF condition. If the month difference is lower than 12, then I want to return my measure year total sales. And you see for the line series, it is exactly the same. The only difference is here the comparison operator. Instead of smaller than 12, we have here bigger than or equal to 12. That's it. Of course, then we have to take those measures and put them on the corresponding drop zones here for the column and line Y axis. Now the core functionality is already there. If I go to my slicer there and select a time period with 12 or fewer months, let me do that, then the line turns into a column chart. Now that is all perfect. However, we have a small problem now. You see when the column chart shows everything is fine, we have the Y axis values here on the left hand side. But as soon as the line shows then the Y axis here on the left hand side. Disappears or at least the values and shows up here on the righthand side. And also the grid lines, well, they are kind of missing and we only have it here on the right. How can we fix that? Well, step number one is that we need to make sure that the maximum of the primary and secondary axis is In sync. So to do that, you can use another measure. So let me go into my data pane, Max, and here you see I use a MAXX function to go row by row over a summarized table that has the years and the months. Then calculate the total sales value that is either returned by the column series or the line series, OK. Of those values, it returns the overall maximum. Now that measure we can use as the maximum for the Y axis as well as the maximum for the secondary Y axis. So let me go into Y axis. And here we have the maximum click there on fx. And then here the field value is going to be that measure Max. Now with that in place, we can do exactly the same for the secondary Y axis. So also there click on the fx button and choose that same measure. Now it's not completely fixed just yet because there's another change that we need to do because the column series values don't show because the column series measure returns basically blank everywhere because we have more than 12 months. But what we can do is go back to the column series measure. Instead of returning nothing, blank, we return zero. And now you see we have the Y axis values also showing but. It is not completely In Sync with that secondary Y axis, so we need to make another change. Let's take the visual. Let's go to formatting again. And then we go here to the secondary Y axis and make sure that we align the zeros. All right, perfect. So the zeros are aligned, and now we can just hide the values in the title for the Y axis. We also don't really need the title, so let's turn that one off as well. And that's it. That is the whole visual. So now if I change the time period to 12 or fewer months, then we see our columns again. And you see the Y axis values are still showing only there on the left hand side. Let's now go to visual #2 almost any report has card visuals. that show the most important KPIs. I got a special one for you where the background fills up to 100%, so that is especially useful if you have KPIs. That show a certain completion rate? How far are we from finishing that project? Now the fill card visual looks like this over here where we have at the center the completion rate and in the back a visual representation of that rate where it fills up to 100%. Now you see when I play around with different percentages how that card visual changes now to build this we can use a new card visual in combination with a measure that returns an SVG image. Now let me show you how it works We're going to start by inserting a new card visual. And then we need a measure that returns an SVG image. Now if you want to know all of the details about SVG, then just check out this video over here. But the main points are the following. First of all, we have here some variables at the top, for example, the fill percentage, which can be based on, well, the actual completion rate of a project. But here I just connected it to that slicer that you see on the on the report page. Allright, then we have a fill color for the filled part and an empty color, which is over here white for the remaining part. Then we have the width and the height of the SVG. And then lastly, we have the filled height. Where we calculate how high the filled part should be by multiplying height times the filled percentage. OK, then Here we have the actual SVG code that gets returned for Power BI. You always need to start. With this initial prefix text and then the SVG code. All right, so here we have two rectangles, you see, that the width of both rectangles is exactly the same as the width of the SVG image. And then for the first rectangle, which is the white part, that's over here, the empty color there we also have the same height. But then for the 2nd rectangle, which goes on top, there we have a different calculation for the height. There we take the filled height, but because SVG counts the pixels from the top left corner. Which is (0, 0) we need to reverse it and say height minus the filled height and that's it all right so now this SVG code returns an image that Is connected to your data set. Now to use it we still have to do a few steps. So we need to take that measure. Then go here to measure tools and here we need to set the data category to Image URL and now we want to use it on our new card visual, so let's just drag it on top. Now the measure that you drag on top doesn't really matter because with the formatting options, we get rid of that call out value. So let's turn the call out value off. And let's also turn the label off. Alright, now we can go to the cards. Here we can get rid of the background and the border and then the interesting part, the images, because there we want to change to SVG image demo then turn the image on and here we can switch to image URL now. an fx button means we can select the measure to return the image that we want. And that is exactly the one that we have built before, that measure SVG image demo All right, and that returns our SVG image. The problem is that the unfilled area is white as well as the background is white. So we have to go here to size and style, turn the background off so that only the SVG image remains. And then also here for padding, I would set it to 0 everywhere for the whole visual and also for the cards themselves we have to set the padding to zero and once you have done that you can resize it. So resize it all the way like this. All right, then here for the image URL, you can also set the size, so here we had 100 pixels in the SVG code. So I just match it with that. And when you resize it, you will see there's still this little empty space there. So the position I would change to left of text or below or above text. No, let me go for above text and then resize it. All right, perfect. Now we can place it right there at the top right next to project management. This is almost what I want because I also still need the text on top of it. So we have to go back to that SVG measure and here we have to add an extra text element. So I'm going to add a new line. Copy over the code for that element, you see here it's text where it starts from the left hand side, where it starts from the top and then we have some options like font size, font family. And the color that we want to use. Now for the font color, let's first return a color like black and see how this looks like. Now that works, however the value doesn't really show in the middle, so let me just resize. The visual a little bit so that it looks like it's in the middle. Now this looks pretty good. And what if we change the percentage to 30 percent? Well, still clearly visible and readable, but we can Improve the design just a bit more if we make that color conditional, which you can also do in the SVG code if we go back to our measure for the SVG and then here we add another variable. And that variable is going to be for the font color. If the percentage is above 60%, then I want to have white, otherwise a dark purple, blue color. All right, Now that font color I'm going to use over here, it's that in black. Then I also have to get rid of these quotation marks. Now you see, it's more in line with the theme that I'm using. And if I change to a percentage over 60%, then the font color is white. OK. And what if you want to have extra values showing in there? That's also possible. We just need to then go back to the SVG code and add another line for the text. All right, so that was visual #2 let's now go to our last visual. Visual number 3 A variation of the funnel chart. Now the standard funnel chart looks like this over here. Now the funnel is a great chart if you want to visualize a funnel. How many inputs of something do you have at the beginning stage versus the end stage? And how does it change from one stage to the next one? Now it could be a sales funnel. How many people saw your ads, then went to the website and from there did an inquiry, booked a call, and in the end ended up buying from you? Or A recruiting funnel like visualized over here, how many candidates did we have, how many resumes were accepted, then how many went on to the next stage to have an interview from there to the assessment and then in the end actually got a job offer. Now for those funnels, a funnel chart is great but sometimes also often misused. So be careful that you know when to use it. Not the standard funnel chart doesn't really give you the impression of the funnel. It's just bars that are centered. Now it is also a bit limited when it comes to the formatting options. For example, the labels always need to show in the middle. We cannot put any information here on the right hand side. And then what shows on those labels is also limited. We are limited to the following options. Let's click on the formatting options of the funnel. Let's go here to data labels. And then here for label contents we have either data value percent of first, previous, percent of first and the data value percent of previous and the data value. Now we have to make a choice and it's also not that we can. Well, let the user choose what they want to see. So while it's not terrible, I think we can do a better job. Let me show you my version. So here you see, this looks more like a funnel because the bars are connected with ribbon lines. And we have flexibility in terms of what shows on the labels and we can even give that as a choice for the end user. so I can go over here, to a slicer then place it there on the top and choose what needs to show. And it doesn't have to be percent of first or percent of previous. This can be whatever you like. The second thing is that if you want, you can also add more information to the side of the bars. Now, let me show you this variation where I took away the slicer. But here I'm showing the percentage change right next to the bars from one stage to the next. So how is it built? Let me show you. First of all, it all starts with a standard stacked bar chart, all right And then we need quite a few measures. Let me show you. We have here for that funnel chart, left, left label, middle, middle label, right, right label Let's start with the main ones, left. Now here we have a measure that's going to center the number of candidates that are visualized in the middle measure, All right, So we can take a fixed number and then subtract the candidate count. Now, we can also calculate that maximum point dynamically if you want to with a measure like this, but I wanted to keep it simple. All right, then let's go here to middle. Now for the middle, we take the candidate count times 2. Now why 2? Because otherwise we would have bars that just get cut off where the center is and I want to, well, flip it to the other side as well, and therefore times two. Now the left measure and the middle measure. Well, that is actually the only thing that you really need to start building in funnel. Now let's place them onto our stacked bar chart. So first, the last measure done, the middle measure. Now if you focus on the dark blue part, you see it looks like a funnel that is flipped, but now we just have to turn it around. So let's go here to the three dots, sort axis, and then we want to sort it in ascending order. All right, now that is better. Now we just need to get rid of the left bars. Well, not really get rid of them, but just not show them. So let's go here to format. Then we can go to the bars. And then here we have to select the left series and choose the color white. Now we could actually use the labels of that first series if we want to dislay extra information or if we want to dislay the stage names onto that first part or right next to these bars of the funnel. All right. But for now, I just leave it as it is. And if I want to move everything a little bit more to the left, well, then we have to go back to that measure where we had 600 fixed have to make it a little bit lower to drag the funnel to the left. Now the next thing that we can do is we'll add the ribbon so that we connect the different bars. So let's me turn the ribbons on and well, initially it doesn't look so good, but that's because here for the left part we want to turn it off. Or at least make it 100 transparent. And if you want you can also play with the height of the bars. So if we go here to the bars select all, then layout. Then here we can say that the space between the categories should be maybe a little bit bigger or smaller And then the values that show here as labels, those of course need to be adjusted because it's twice the number of candidates because we had to multiply it by 2 to get the actual funnel to show correctly. And so therefore we have to go back to data labels. And then here you select the series middle. And then here for the value there we can place any other measure. Now that measure I already prepared. So I'm going to go over here and select middle label. And let me just make sure that the last one also shows. There you go. Now how is this so much better? Well, first of all, it looks a bit more like a funnel. But second of all, we have more flexibility when it comes to formatting. And we have also more flexibility to say what should show and where should it show. Now in this variation of the chart you see I'm using Fields parameter there at the top to switch between different things that show on this funnel. So I can say percentage of first. I can say show me the percentage of the previous stage. Or show me just the change from one stage to the next. I have full flexibility. I'm not limited to those options of the standard funnel chart. Now for that functionality a Fields Parameter, label switch. Let me show you which lets me switch between these three different measures that I've set up And then I have another measure that checks which one is selected in the fields parameter and then returns over here the correct measure. And that's it. That's the one that we can use on labels. All right. So if I go back here to that funnel chart, then go to formatting, then here data labels, and here we want to have the series middle selected and then go to value and then here instead of middle label I'm going to choose middle label dynamic and now you see we display the same values as there in that finished version and what if I want to have extra information on the right hand side or on the left hand side of this funnel? Well, then we just add another series to the right of it and then we can make the background white and use the labels to show whatever information we need. All right, so these were my 3 charts. Let me know in the comment section below if you have ever built one of these chart variations like this before and if you have other unique chart ideas that I would love to hear that as well. Now if you want to build reports together with me from beginning to end and then all of my tips and tricks, then check out my upcoming design training over here. And if you just want to watch more visual design stuff in Power BI then check out these videos over here. All right, I want to thank you for watching and see you in the next video.