Add Several Calculated with airSlate SignNow

Move calculated field

Welcome back to the Tableau help videos. In this video, I'm going to show you how to create calculated fields as well as do some more visualizations and add some more useful information to visualizations that you've already created. First I'd like to show you how to create a calculated field. So a calculated field is just like one that we would have used the builder for when we were doing Access, however we're going to want to create calculated fields inside Tableau so that we can use the data over and over again. There's not a place to build per-se gross or net sales for each individual visualization, so instead we build them for our Tableau workbook as a whole and then we're able to use them for each one of our visualizations. As you'll recall, the ones that we use most often for builder in our BA 342 database are gross sales, net sales, and total discounts. Let's build those into this workbook. In order to build a calculated fields you'll go to the analysis tab or the analysis drop down and you're going to say create a calculated field. When you say create a calculated field, you'll get this dialog box that says, "Ok awesome you want to make a calculated field, well what do you want this to be?" So in order to start we're going to just do gross sales. Gross sales as you recall is unit price per times quantity sold. When you start typing in this calculation box Tableau is smart enough to figure out what it is that you're wanting. I want price per. (I know that because I've used this database time and time again.) I want price per and I want to multiply by the quantity sold, so I start typing quantity I get this quantity sold field. I can double click on it or I can push tab and it will automatically put it up here in these brackets. Can you type it in the brackets? Of course you can. There's lots of ways to get it into this field but Tableau is a really smart tool and if you just are typing it, it's going to give you the correct options. This calculated field box also has something nifty where it says the calculation is valid. It says the calculation is valid because I have price per which is a number times another number and it says ok I can deal with that. So let's hit OK. You'll notice that gross sales appears down here in our measures category which is something that we would do math with so that's the right place for it to be, but notice that it is here. Let's create net sales. In order to get net sales, I go up to analysis click create calculated field. I'll type net sales but I'm going to show you a different way to get it this time, so instead of starting to type I can also go over here to my pills I can click on them and drag them into the box. There's price per times quantity sold, nope, times and then I'm going to open my parentheses and do 1 minus the item discount. Now you recall this because we learned this formulas when we were doing logic in Access, the formulas are the same price * quantity * (1 minus the item discount) because the item discount is stored as a percentage. If it were stored a different way the formula would be different but that's what it is for this one. And as just like with gross sales, net sales appears over here as well in our measures category. So we have one more where we want to know the total discount for each item or for each sale. So create calculated field and we're going to have discounts or total discounts and then we're going to say item discount (can't double click on it) got to drag it over item discount times price times quantity so again you can start typing the field names or drag them over your calculation is valid and you click OK. So we're going to do a visualization that shows us the sales discounts, the gross sales, the net sales and the number of sales by customer country. So let's get started with that. In order to do that, we're going to create a new sheet and we're going to start with customer country because that's ultimately how I want these to be sorted. You'll remember from a previous video and from class that we want to start our visualizations by dragging our dimensions to our rows card so we'll drag customer country to the rows card and for this visualization we want the total sales, the gross sales, the net sales, the sales discounts as well as the number of sales by customer country. So we've got a lot going on here we want to get gross sales in a column. We want to get net sales in a column. We want to get total discounts in a column, and then we want to get a count of the sales. So in order to get a count of the sales as you'll recall we want the sales invoice ID from the sales table. We're going to drag it up to column as well. It's gonna get really mad and we're gonna do this add all members thing again and we're just gonna wait. It's gonna take a second and we're gonna say, "No, sales invoice ID I really just want you not to list all of them but I want you to give me a count of the individual ones." So now you have this bar graph which is you know relatively useful but not really because who wants to read all of these things in a bar graph so we're going to create a text table that shows us this. Again your show me button is likely here if you're on a Mac or you have a small screen like I do you can push ctrl one it'll show up. We want a text table, so you click on text table and then close the show me so that you can visualize this data. Hopefully you look at this and you think that something is wrong. When I look at this the first thing that I notice (if I don't drag things too far and get rid of them all) the first thing that I notice is gross sales, net sales, and total discounts aren't dollars which is really going to drive me crazy because I'm an accountant. I know that all of these things need to be in dollars. The reason that they're not dollars is because I didn't tell them to be in dollars, so I'll go down here to gross sales click on this downward arrow and I'm going to tell it every time you show me this I want your default properties to be dollars. I'll click on the down arrow I'm gonna click default property's gonna go to a number format and I'm going to click currency standard. Okay, so you'll notice that gross sales automatically changes its mind and says okay great I'm dollars. Just like Excel it now gives me these pound signs for the ones that are too long, but let's go ahead and fix the other two before we decide how big we want to make these columns. The great thing about Tableau is it's very smart and it will let you do multiple things at a time, so if you click ctrl you click on net sales, you click control and you click on discounts it's going to give you the option to select both of them and then you can select this down arrow default properties, number format, currency standard, and they'll do both of them for you at the same time. Now if you didn't follow that you can certainly do them separately without a problem but it's easier just to do them at one time. You remember from a previous video that we can do grand totals as well, so let's put grand totals of gross sales, net sales, and total discounts over here. A couple ways to do that, I can click on the analytics tab grab totals and drag it over or if I'm not a click-and-drag person I can go up to analysis click totals and say show column grand totals and then it will happily do that for me. I'll expand it a little, so I can see them all you'll notice now I have this "distinct count of sales invoice ID" which you know is not what I want it to say. I can right-click on this and I can do edit alias and just say number of sales and it will fix it. It will take it over there (for whatever reason) but I can bring it back to number of sales and then I can expand this first column so that I can see all of customer country. So now if I click off of this table (because if I click on the table I'm selecting data) but if I click off of the table I can see everything together that's my number of sales. I know that because I've seen that number many times as we went through BA 342 company all of these are correct, We're going to call this measurements by country, so we're gonna rename this to be measurements by country, and you'll recall that when I rename it down here and push enter it's going to rename my sheet as well. It'd be very very helpful if I spell it correctly so let's put another e in here in measurements where it goes and then I will have it spelled correctly. The grand totals are very helpful on here. We also could add a rows total which isn't really that helpful right because if I'm gonna add number of sales gross sales and net sales together I probably don't want to add those but just be aware you can add the rows total as well. Hopefully you now understand how to add calculated fields and create this table as well as to create these dollar values and get them formatted the way that you want. I want to point out that there are also ways that you can filter, so if I wanted instead of right now my customer countries are in alphabetical order let's say I want to change how this is filtered and I want to sort by the one with the most the highest number of sales I click this filter button and it will change the way that these are aligned. We'll click back and go back to alphabetically but just notice you can click in any of these and change the way that things are filtered. When we move into another video we'll work on dashboards and some other visualizations. Please stay tuned and please follow our other videos as you study for the tableau portion of your class.