[MUSIC] Welcome back. In our previous lesson, we went through an example of creating a simple KPI with a calculated field. In this lesson, we're going to create more complex data visualizations of KPIs using Tableau. While what we cover in this lesson is complex, it's not that difficult. Once you see it, you're going to be thinking, my goodness, this is so easy. It's pretty cool and this is really the power of Tableau. After this lesson, you will be able to get to a complex KPI by calculating a field off another calculated field. You will also be able to find a field that maybe missing from the list of fields. So, let's get started. [MUSIC] We went through an example of creating a simple KPI with a calculated field in the last lesson. We're going to create more complex ones, but it's not that difficult, and once you see it, you're going to be like my goodness, this is so easy. It's pretty cool. And this is really the power of Tableau. So, I assume that you have the previous KPI example up on your screen, now, like I do. In fact, this is really just a continuation of what we did in the last lesson. We're going to be creating three more KPIs all around profitability. The first one is profit ratio which we've done before. But just in case you haven't, let's show the process very quickly. You really should have the previous KPI example up on your screen. If not, do that now like I have it here. In fact, this is really just a continuation of what we did in the last lesson. We're going to be creating three more KPIs all around profitability. Of course, any company wants to make profit so this is the primary KPI that a lot of companies will be using. So it will make for a very useful example. The first one is going to be around profit ratio, which we've actually used before. Technically speaking, this is not a KPI, but it's a way to create a KPI and this is often what you'll do. You'll create a calculated field and then you'll create another calculated field off of the calculated field you just created. We've used proper ratio before so I already have it on my screen as you can see here, but just in case you don't I will show you the process. What I'm going to do here is I'm going to right click on any of the measures. You can do profit or whatever. And then you'll see Create and then go to Calculated Field. And then what will happen here is that you will see the profit ratio formula in front of you which is just the sum of profit divided by the sum of sales. So the sum of profit divided by the sum of sales. Again, you know how to do calculated fields, so I'm not going to spend a lot of time on that. Just call it the proper ratio, and then we'll be all set. So now, once we're done with that, we can go to the next thing which we need to do is we need to right click on Profit Ratio. You could also click the down arrow by the way, you could do either. I want you to go to Default Properties and then I want you to go to Number format. And this Number format, I want you to change it to Percentage if it's not already changed to Percentage. Notice mine is. And I want you to add one decimal place. It's up to you if you want to keep it a zero, that's fine. Let's just do one decimal place and click OK. And we're all set there. So now, we go to the KPI, the visualization of the scatter plot. And so I want to now create another calculated field. And so this is again using a calculated field to create another calculated field. So do the same thing. Right click. Create calculated field. Now I'm going to give you a formula, but this is something that you should be able to do also, which is profitability KPI is what I'm calling this calculated field. I want you now to just enter the following field in your calculator which is, as you will see, on the screen. And this is an If statement, so it has to be an if, then, else, and statement. So it is if Profit Ratio is greater than 0.33, in other words one-third, then quote unquote, great. Else if Profit Ratio is greater than 10% which is 0.1 then quote unquote OK else quote unquote BAD. And make sure that you have the end at the very end of the if statement. So it has to have an if at the front, it has to have a then. It has to have an else, and then it has to have an end. So that's just a primer on a standard if statement. It's not uncommon. It's virtually everywhere. It's how you do the if statements. So once you have that formula all set up, you can click OK and then we can move on. But again, here is the formula. If proper ratio is greater than a third, then great. Else, if it's above 10% then OK. Otherwise, it's bad. Then make sure you have that end on the very end of the formula. So click OK on that. And then we can move to the next step. So this is the profitability KPI here. And once we have the profitability KPI in front of us, we can then use this profitability KPI for our various visualizations. So now what we're going to do is we're going to create another sheet. And then we're going to, Drag, Profit over to Columns. And then we're going to drag Profit Ratio to Rows. So if you remember, we have to do something with scatter plots. We have to remove the aggregate measure components. So what we do is we click on Analysis at the very top and then we uncheck Aggregate Measures. I've explained why before, so I'm not going to do that again. But just do that. Click on analysis and then uncheck aggregate measures. And then you get a scatter plot, as you can see here. And so what we're going to do now is we're going to make the color, make it nice and colorful, so let's drag the product name first to detail. And the reason I'm doing that is I wanted to make it readable. So that when an end user is using this, they can hover over a particular circle and see information about the particular field. So as you could see here, we have the product name right there so it's very nice. So the next thing we're going to do is we're going to drag the profitability KPI and put it over the color field. And there you go. But isn't that weird because it's like bad is blue. OK is red, Great is orange. That's not intuitive. So let's click on Edit Colors and we're going to just change the colors. And it's very straightforward, and just follow the steps I'm doing here on the screen. Which is, to change the colors you click on green for great, yellow for OK, and we'll do red for bad because that's a trigger there. And then, that makes sense, right? It's just like a traffic light. And we'll click OK once we're done with that. And then we'll move on to the next step. So once we've designed the KPI we can see, okay, look at this. Look at this beautiful thing which is a scatter plot. Which shows red, yellow, and green just like a traffic signal. So great is green, OK is yellow, red is bad. Very problematic. So let's go ahead, so that's our KPI there. We have a low KPI. And then I'm going to just do some formatting here. I'm just going to change the shape to a circle instead of a circle. Fill circle instead of a circle that doesn't have any fill in it. And then I'm going to just make the circles a little bit smaller like so, and then we have our KPI. I'm going to rename this now Profitability by Items. So Profitability by items is what I'm going to rename to right now. And there you go. Next, I'm going to create another sheet like so. I'm going to drag Segment to Rows. I'm going to drag Categories, and then I'm going to drag Customer ID. Where is customer ID? Where is it? I feel like I knew where customer ID is, but now I can't find it. Is this a time to panic? No. It's not a time to panic. On some of yours, you may actually have Customer ID but this is just an exercise I'm just showing here which is if a field is not in there and you think it is, don't panic. We're just going to make sure that it's there. And what we're going to do is we're just going to go over to the data source. Remember the data source where the information is held in a spreadsheet. We can click on the data source like so, and then we're going to show the hidden fields. We're going to check that and see what happens here. Show hidden fields. Hopefully there are hidden fields because otherwise we're in trouble. Check and then it looks like they're older hidden fields. Look at that, Customer ID. There it is. And so this customer ID field is very important. I'm going to unhide it. Did you see that? I just right clicked and unhid it, so that it's now available for you. So, I'm going to go back to that sheet. And then I'm going to find it in the list. It's either in measures or dimensions. So, I'm going to just scroll. There it is. Customer ID, I'm going to drag it up to you. The customer folder just to make it convenient for me. You don't have to necessarily, but just more convenient that way. Just stick it up there. And now I'm going to drag that one to the rows as well. So now we have three field on rows and that's not unusual. Oftentimes, you're going to have situations where you have a bunch of different fields in columns or rows. So now what I'm going to do, and this is kind of a cool trick which is I'm going to do some double clicking. And let's see what happens when you do some double clicking. Let's double click on Profit. Sales and profit ratio. And see what happens. Look at that. So if you see what happens here, it actually puts all of those fields into measured values. So that they're in the columns and it automatically puts the measure names in the column shelves. So this is very nice. And so now what I'm doing and you can kind of see what I'm getting to here. You drag the Profitability KPI over to color again and then it automatically changes those colors to the color scheme that we had from before. So there is the color scheme there for that one. So I'm going to just rename this particular one Profitability details. And it created another worksheet, wow, we're creating tons of worksheets here. And what I'm going to be doing here is drag Category Profit to the text and Profit ratio, so now you see I double clicked actually Profit and Profit ratio. Category is a rows and then, what I'm going to do now is I'm going to do something really cool, which is a table calculation. Table calculations are really cool because they do a lot of stuff and it's downright magical. So I'm going to go to the measures pane, and I want you to hover over the sum of profit like that. And click on it. And select Quick Table Calculation. And then select Percent of total, like so. Do you see that right there? Look at that [SOUND] now, it is time to do a little bit of formatting. So we're going to change the name of column to Percent of total profit. Instead of the extremely long and not helpful name you see here that Tableau automatically generates. So, that's how we just do that, you just keep it on screen there. So then we're going to drag the profitability KPI measure again. And then we can, of course, we'll be able to see the great, okay, and bad, as we did before. And then I'm going to rename the sheet, so when we get to it there. Rename the sheet Profitability by category. The final stuff I'm going to do is, I'm going to save my Tableau work space as a new file because we're going to be using this. And we're going to come back to exactly the same file to work on dashboards and other advanced items. So stay tuned. So you could choose how you want to name it, I'm going to name it however I feel like here. And this completes my demonstration of creating complex data visualizations KPIs using Tableau. After this lesson you should now be able to create complex KPIs by calculating a field off of another calculated field. And you should also know how to find a field necessary to complete a complex KPI visualization if it seems to be missing from a list of fields. So see you very soon in the next lesson. Take care. Bye.
