We'll start by connecting to a variety of data sources. This will leave you with some great workbooks to practice on and develop on your own after this course. And then we'll take a deep dive into dashboards, including preparation, summarizing data, and and interacting with data for dashboards. In module 10, we're going to focus on more calculations, summarizing your data for getting them ready for dashboards. In lesson 1, we're going to focus on arithmetic..
And in lesson 2, you're going to learn how to group your data with calculations. Now, we did manual groupings before for our regions for our map, but this is using calculations to do grouping. It's, it's pretty cool. In Lesson 3, you're going to learn about how to display correlation with calculations. And before we get to that lesson, we'll return to this slide show because the calculation for that one is really intense. In Lesson 4, you'll learn how to use crosstabs flexibly. And in lesson five, we're going to cover simplifying your business.
Rules with custom calculations. And so there is a Word document in the video description named calculations. And I have both the calculations for lessons two and three in this module in that Word document. So you don't have to type them in, but we will review them when we use them. I already have a new sheet ready to go in our sample superstore live file. And we're going to start with arithmetic. And you're going to learn how to do an ad hoc, also known as an inline.
Calculation during this lesson. So we're going to drag, let's start populating this a little bit before we do our calculation. And we want to drag market segment to rows. And then what you're going to do is you're going to double click in the column shelf And notice it creates that little long oval for you to type into. And we're going to type in A V G, or A V, and then you can tab it in from the list. And then we want the profit field, so I'm going to grab that one..
And then I'm going to do the slash for division. I'm going to type AV again and grab AVERAGE again. And this time, I want the sales field. So you have AVERAGE PROFIT divided by AVERAGE SALES. which actually equals a profit ratio. So notice at the end, it says in order to apply it, you have to do control enter. So I'm going to do control enter there..
And now you see our visualization is populated. Now we can right click on the axis so we can change the name of it there by going to edit axis. And at the bottom, Let's name it and it's going to sound weird for a moment, but let's name it our profit ratio and we can go ahead and close. The edit access dialogue..
So the reason why I didn't just name it profit ratio is because there's already a profit ratio field in this data set. And I just wanted you to be able to do an ad hoc calculation. So there's already a profit ratio field in the data set. And we want our profit ratio to display as percentages. So what we're going to do is right click on any column in your viz. and choose Format. And the Format pane opens on the left side of the screen..
And we're going to go to the Fields drop down, and we're going to select our Ad Hoc Calculation. And under Scale we're going to do the Numbers drop down and choose Percentage. And we'll get rid of the decimal places. So now you see your axis numbers have updated to percentages. And let's go ahead and name the sheet Average Profit Ratio per Segment. And on your own, let's go ahead and change the axis title to Average Profit.
Ratio and then save your workbook. Now I'm going to go ahead and close the format pane and I'm going to click on color on the marks card and I'm going to just go with my usual orange color. So we're ready for a new sheet where we're going to do some more arithmetic. On this sheet, we're going to calculate cost. So, let's start by dragging the order date field to columns..
And we want it to display the quarter of the order date. So, whether you want to expand it or drop down and change it to quarter is up to you. But the only one we want up there is quarter. Let's double click in the rows shelf and we're going to type sales minus and then profit and go ahead and control enter to apply. Now this One, we're going to treat a little bit different than the one we did just a few minutes ago..
Posts Related:
- Painel de controle moderno do Excel Sistema de análise de vendas Tutorial em vÃdeo #1 Office
- Excel 2016 Advanced
- How to Design a Certificate of Achievement in MS Word DIY Tutorial
Let's go up to the rows shelf. - ID Card Make
And this time we're going to click and hold on sum of sales minus profit, and we're going to drag it over to our field list and drop it. So once it's in the field list, you can rename it. It's already highlighted for you to rename and we're going to type cost and press enter. So now if you look up at your row shelf, it's the sum of the cost..Now we're not done with this visualization yet, but let's go back to our previous sheet so we can do the same with our average profit ratio field. Go up to columns. And you're going to drag it and drop it to your field list, and then just rename it Average Profit Ratio. So that way it's available if you want to use it on other visualizations..
And we can go back to our latest sheet. So one thing that we're going to do is we're going to fix the axis so that it is continuous. So, let's do the drop down next to quarter order date. And instead of it being a discrete field where it's showing each quarter like that, we're gonna choose continuous. So it still works if you hover over any point, any mark on your line, it tells you what quarter it is. But the axis just says quarter of order date and has like 4..
It's a continuous line as opposed to discrete. So I want to point out some interesting information down in your status bar, on the left side of your status bar, where it's showing you the information from your visualization. Which is pretty cool at a glance information. So all the way down there, it's letting you know there's four marks. We have four quarters, so there's four marks on your line chart. One row by one column. And it gives you the sum of the cost..
That's over two million dollars. So good information at a glance. Let's give this viz some more detail. So let's add profit to the rows shelf and add market segment to columns. And then we decide we want a double line chart. So I'm going to go to show me, or I should say a dual line chart, and I'm going to choose the dual line chart..
So we can see both of those values. for each segment in the same pane. I'm going to go ahead and collapse. Show me. And we're going to change the colors of the legend on the right in a moment. Just so you know, just something to keep in the background. If it ever doesn't generate a color legend for you on the right, you can make it do so by going to your analysis. menu,.
And you can hover over legend, and it wouldn't be checked here, but you would be able to check it to get that color legend. And the same for a shape or a size legend. If they don't show up automatically, you can grab them from analysis menu. So I'm going to go over to the legend and do the drop down. and choose edit colors. And I am going to select a palette. The palette I'm going to select is called, it's all the way at the bottom, it's called lightning watermelon..
And then I'm going to click on the cost field and I'm going to change it to red. And for the profit field, I'll change it to green. Actually, I want a darker red on cost. And then I'm going to choose OK. Let's go ahead and change it to month of order date. So, I'm going to do the dropdown, and I'm going to select the second month on the list. And you can see how it updates our lines..
They're now showing monthly. And then we're going to show a filter for the order date. So, I'm going to just right click month of order date in the columns shelf. and choose show filter. So it shows up on the right. And it is, you know, a slider. And we want to do the drop down to the right of our filter header. And we want to choose start date instead of range of dates. So on the filter, instead of using the slider, I'm going to click right at toward the end of January, 2019, and I'm going to just manually edit it to.
Tell it to start with January, 2021. And you can see how it adjusts.
And I'm going to clear that filter so I can right click on it in the filter shelf and clear it. Now, if you want to give your end users the ability to filter, then go ahead and show the filter again and just leave it there so that they have that option to filter if they need to. So they can see. whatever they need to see. So I'm going to just add it back and change it back to start date..And lastly, we're going to do just a little bit of formatting on this visual. And so I'm going to right click anywhere in the chart and go to format. And as usual, it opens on the left side, and I'm going to go to the paint bucket up at the top of the format pane to get to shading. And I want to shade this worksheet. I'm going to go to more colors. And I'm gonna choose like a really pale ish green..
A little bit paler. Yeah, very pale, very subtle. And click OK. So that really adds, for lack of a better word, some drama to the visualization. Makes it more eye catching. People will be more interested in viewing it. Little color goes a long way. And we'll end by naming this sheet Monthly Cost and Profit by Market Segment.
And Save. So even though Tableau saves in the background, I always like to save along the way. And I'm going to go ahead and get rid of the Format pane. We don't need it anymore for this portion. And go ahead and get a new sheet ready. So I have on the screen that calculations word document, and we're getting ready to group data with calculations. So instead of having us type in these calculations, I put them in word..
And so let's grab the first one. I'm just going to start with the if. And once we get started with this lesson, I will, of course, break down this calculation for you. And by the way, this is in Word, so I changed it so it is straight quotes instead of smart quotes. If you have smart quotes, it won't work in our calculation editor in Tableau. So I'm going to go ahead and copy that first calculation..
And on our new sheet, let's start by dragging category to columns. And subcategory to rose. So the thing that happens here, right, if maybe there are times where you just wanna see the subcategories and other times where you just wanna see the categories, right? And you don't wanna have to keep deleting the pills in a visualization. So the calculation that we're gonna do is gonna create dynamic grouping and ungrouping..
And so we can delete both of those pills from the shelves. Just wanted to give you an explanation of what's going to happen here. The calculation we're going to build will allow us to expand or collapse. So, collapsed would just be showing the categories, expanding would be able to see the subcategories. So you don't have to, like I said, delete a pill to change that kind of viewing. So what we're going to do is we are going to create a parameter..
So I'm going to do my drop down arrow and create parameter. And we're going to name it. group and ungroup. We're going to give it a data type of string and we're going to allow a list and we're just going to add two values to the list. So the first one is group.
And the second one is ungroup and we're going to go ahead and click okay. Now we're going to create a calculated field and I can collapse that right side. And we're going to name this calculated field dynamic grouping slash ungrouping..
And this is where we're going to paste the calculation. So, let us discuss this calculation. I'll make my box bigger. Okay. So, we're using the if, then, then if. Then else, else if statements with or statements mixed in..
So, if the subcategories equals bookcases, or chairs, or furnishings, or tables, then if the parameter we created is set to group, then it will group them together. and call them furniture, or else it would just show the subcategory. If none of those things are true, it would just show the subcategory. Then we have an else if block in the middle. So this is the same. It's saying that appliances, art, binders, envelopes, fasteners, labels, paper, and storage, and supplies would be grouped together as office supplies..
Or, if that's not the case, it would just have the subcategory name. The last else if block is creating another grouping. So this one is accessories, copiers, machines, phones. Right? They would be grouped together as technology. And then it has else subcategory. So if none of those are true, it would just show the subcategory. Then it ends that portion. Then it gives another else calculation, which saying if none of the above.
Are true, just show the subcategory. So everything from bookcases to phones. If none of those are a match, just show the subcategory. So, I didn't want you to have to type all of that in, so that's why I put it in the Word doc. We're going to go ahead and click on a OK. Alright, so now we're going to use our dynamic grouping, ungrouping calculated field in columns. Now we do want to show our group and ungroup parameter, so I'm going to right.
Click on it and choose show parameter. So now it defaults to group, so we have our, these are actually our category fields, right? But if we go over to the parameter and we choose ungroup, we just get the subcategories. So it's not like you would normally, like we saw when we, before we started, this was you can have category and subcategory, but if you were to collapse it, you're.
Still going to see the category, right? So this way it gives you a choice of either seeing by grouping, you're seeing the categories by ungrouping. You're just seeing the subcategories. So this is another useful interaction that you can have for your end users. And let's go ahead and name this sheet. We'll call it dynamic.
Group, dynamic group. And then I'm going to do a dash. And it's going to be sales information. And I'm, as usual, going to change my color. to my favorite orange here to give it some more visual appeal. So in our next lesson, we're going to figure out if there is a correlation between sales and profits per category. Before we do so, I have to give you some background information on the calculation..
So a correlation coefficient is a value that quantifies the relationship of two or more variables. In linear correlation, the coefficient quantifies the strength and direction of the correlation between the variables. One type of correlation coefficient, and that's the type we're gonna use, is the Pearson Product Moment Correlation Coefficient. It's also known as R, which measures linear correlation and provides a value between negative one and positive one. Positive one is total positive correlation..
Zero is no correlation. And negative one is total negative correlation. If two variables have a strong positive correlation, then the r value will be nearer to positive one. For example, x increases as y increases. If they have, have a strong negative correlation, then the R value will be nearer to negative one. X increases as Y decreases would be that example..
A strong positive correlation is generally considered to be a value between positive 0. 8 and plus one. With anything below positive 0. 5 considered to be weak. So for those of you unfamiliar with correlations, just a reminder, this slide deck is in the file for video of the video's description. So to measure R, the strength of a correlation, The covariance, which is the.
Dependence between variables needs to be determined and then divided by the product of the variable's standard deviations. There are a few different types of formula to determine the correlation coefficient. We're using the below formula, which means in terms of data that n would represent size. X would be profit and Y would be sales. This formula is pretty daunting, but once you break it down into parts, it becomes much easier to understand..
So on the lower left of this slide, you have the entire calculation. And on the right side, you have it broken into four parts, which we're going to review before we do this. For the first part, we'll be using Tableau's size function. which returns the number of rows in a partition. For the second part, we'll be using the window sum function, which returns the sum of the expression within the window. And the window is defined as offsets from the current row. For part three, x i equals the sum of profit and mean of profit..
For example, the window average of the sum of profit and Sx is the standard deviation of profit. Therefore, we would need to subtract the mean from the sum of profit and then divide that by the standard deviation. And you'll see the line of the calculation that will do that. And then part four is almost the same as part three, except we have to swap profit for sales. Once we have all of the parts..
You'd need to multiply them by each other. And the reason why I put this in the calculations is because parentheses is everything with this particular calculation. So I put this in the Word document so we can copy and paste it and make sure we get it right. But there is a reminder to make sure to take special care that the parentheses are in the correct places. So then I have the final formula, which is in that Word document. and a screenshot of the whole thing put together at the bottom. Now we're ready to go in and do it..
So now in that Word document, I'm going to go ahead and select the calculation at the bottom and copy it. And then I'll go over to Tableau and we're going to create a new sheet. So again, we're looking to see if there's a correlation between sales and profits per category. So we're going to put category. And profit in rows and we'll put sales on columns.
And we'll use the customer last name for detail. Now we're going to go ahead and create our calculated field and we're going to name it correlation coefficient. and we can paste in our calculation. So it's one divided by size minus one, right?.
Then it's times the window sum. Yeah, this one is pretty deep. So I broke down the different pieces. There's four parts to this and you can see that they're all multiplied to each other. Let's go ahead and click okay. And before we use it, let's right click on it in the field list and edit it. So, you notice when we come back in, it says the results are computed along table across, and it says default table calculation..
Well, table calculations were covered extensively in the introduction course, but I will tell you that they have two characteristics, partitioning, which is also known as scoping, in this case it's partitioned. On the table, and then the second part is addressing also known as direction. And it's going across the table. So let's go ahead and click. Okay..
Again, and we're going to drag correlation coefficient to color on the marks card. And so you'll notice that nothing really happened, right? Kind of dimmed out a little bit. And that's because we want that correlation. coefficient to be computed using the customer last name. So in order to do that, we're going to right click on its pill in the marks card, hover over compute using. And choose customer last..
So we're using a specific dimension for it. And so right now the darker, the color is an indication to you that it is a stronger correlation. I'm going to go to color and edit colors. And I want to choose a different palette here, so I'm going to choose.