Aggregation, Formatting, Totals, default proper in Tableau

Dan We
A free video tutorial from Dan We
BI Expert, Trainer, Datalover
4.4 instructor rating • 51 courses • 42,458 students

Learn more from the full course

Tableau Masterclass - Advanced calculations with Tableau

Learn and understand how to apply advanced formulas and calculations in Tableau today. Tableau training for you

02:32:24 of on-demand video • Updated October 2020

  • You understand and know how to use advanced calculations in Tableau
  • You fully understand Level of detail expressions in Tableau
  • You made a big step towards becomming a formula expert in Tableau. (Notice becomming a real expert requires working on your own projects)
  • You can leverage what you have learned and use Tableau even more efficiently to impress coworkers / superiors and competitors
  • You might open up new career opportunities for you which are not only highly rewarding but also offer more job satisfaction
  • You can generate valuable insights for yourself and your company
English [Auto] Hello and welcome guys to the first video to the table begins to advanced formula course. And before we start just let me briefly tell you that even though I'm doing it a certain way with the formulas here this is not necessarily the best or the only way because the great thing about tableau as well as probably So all these state undertakes tools is there are often several ways how you can create your solution OK. So that's just at the beginning. OK. Now let's get into it. OK. We basically start with some simple formula. So what can we do with tableaux and what First you need to load our data. OK. So in this case we've got Exel files so I'm going here to connect and I'm connecting to an Microsoft Excel file. Click on this and then I say I want to connect to my net sales for instance. OK. I click on Open simply go there. Just wait a second and we get a preview OK at my table. That's sales. Here it is. And here I got my fields. So my columns and my table I got a country and you can also see that the table has already added some kind of kind of data it is in this case it's a string value for instance it's date field and your visa. This is an American value. You are going to string OK. And this simply is. In this case this is a geographical field. OK. All right. Now we go to a worksheet and we take a look at our data. So in this case let's say I want to see my country. So if you want countries you can simply drag and drop them to Rozier and Rick Katz an overview of all the countries we have and now we could add some data. And in this case the data we have are the sales. Right. So what I could do is the following. Either I could simply drag my sales to drag and drop them to the columns and then I get by default. This kind of watch. Right. But in this case that's not what I want. I want to see the values itself. OK. So what I could do instead is do the following. I get all of that. And what I can do is either use the sales and direct them to text. So in this case I see exactly myself's the other way I could do it is if I instead of dragging it to text here I could say get rid of that and I could say I clicked on sales OK simply click on sales and you can see you've got exactly the same. OK. So whenever you want to see the values you've got those two options to either drag them to text or simply double click on the field. OK. All right so what else can we see here. Well what we see is that we get the sum of sales right. I can double click on it and you can see this is what the field looks like. OK. It's the sum of sales. So by default Tablo is always aggregating the data as the sum. OK that's a default behavior. So if you want to change this what we could do is we could go in here and we could say so simply click on this little white arrow here and say I don't for the measure I don't want to see the sum. Instead I want to see something else. And we got a lot of different options. So also from statistics like personalized thenet deviation variance and so on but let's go with the default one scale with basic ones that say for instance the average IF want to see the average simply switch from some to average on average and you know you can see the average sales per country. All right. So this our dataset. OK. So what else do we have. If I go back here and say instead of the average year I want to see another measure I could say the medium for instance if I took a median then would get the median and just by the way in case you're not that into statistics and you want to get into it of course then I just can tell you that whenever you get a huge data sets and you get a lot of outliers then you probably would rather go with the median than the average. OK. That just I guess most of you already know this but just wanted to mention it. All right. So these are two measures we have. Then we also have accounts and we have count distinct. OK the difference simply is that count simply counts the amounts of data or Rosenow data set in this case and count distinct simply count to distinct values right. So that's pretty easy to differentiate. So depending on what you want simply select the measure here and you get the value. OK. All right. So what if I want to see an additional measure a year for instance. Now I got my count of cenotes what if i also wants to see the sum of sales. How can I do this. Well what I could do is I could simply go in here and commit and you can see I get my count of sales as well as my sales year. OK. But my two measures and you and I cut them next to each other. So why do I get them next to each other here. Well depending on you can already see it in the columns we get measure names. And here in the text you get measured values. So these are two options which are created automatically by tableau. OK. So whenever we have several fields here this is what we can do. We could simply use the measure names and drag them in here. If we don't do it if I get rid of my measure names year then you can see it's here on top of each other. OK that's not what we want. I could also get rid of the measured values here and now I get all my data is lost. OK. So if you go back simply use this control on Booba which is by the way the most often used in Tablo that just wanted to mention that because I find it really funny but this also tells you that you can explore your data and that's what you should do. OK. So let me go back in here and let me go back in here. All right. So this is when you want to have the metters next heats up each other. Your For instance either other sales than the sales and of course you need to have a measure or names in here and you also need to have the metal values in here. So if I get real metal values that mills will get rid of Muslim names here. OK. So one more thing I wanted to tell you is the following. Let me also look beyond sales here. And as I said before we get the some of sales here with this DoubleClick This is the default aggregation. So what if we do not want to have an end to any aggregation. So for instance I want to see each of the sales entries for a --. How can I do this. Well you can go to analysis here and can say I do not want to aggregate the measures if I click. Basically this year then what you see is you get see the individual entries in your data is it OK for a venue for instance 3 entries. So those three and current These are simply the sales. OK sales. There's no Some you can see here. So these are simply not aggregated. So if we get our aggregated data like the somebody else we could go back in here to analysis and then we can aggregate the measures and they can see these eleven thousand six hundred forty two is simply the sum of all the three entries we have. OK. So and that's true for any other kind of metric you would create. So this is the way you can disaggregate your measure what you can also do appear and analysis is you can see the totals in here. For instance I could say by default if I scroll down you can see there is no. In this case column grand total. So I can see the total sales here. So if I want to add this I can simply go to analysis here and I could say I want to see the totals at. I want to see for instance show me here the column growth totals. OK. So if I click on that I check that. And now I scroll down and now you can see I got my rent totals here. OK. So that's also what I can't do here. If I want to place it on top that's also possible go to analysis back. And here you can say for instance here in total. And then you say I want to see the column totals to top put it in here and you can see it's on top. OK. All right. So that's what we can do. Then one more thing I want to mention here is the following as I said before and as you have seen before the default aggregation method is a sum of sales. So what if I want to change this. You've seen it. I could simply go in here and can say instead of some here go to measurers I want to see this Kanes for instance accounts. OK show me the accounts. So count the values for each of the countries the entries of sales I can do this. But what I also can do is set up this one click on it you can see DoubleClick. I got to say it's again. OK. So what I also could do is the following I could say going here sales and can change the default properties if I go to different properties here you can see there is an aggregation method. And by default it's a sum. So I can change this year as well. So for instance if I have many sheets and I never want to use the sum I always want to use for instance the everich then I could change it here to leverage now. And if I now at this again to my visualization if I double click on it now you can see I got ever just sales. OK. It's still called sales because we have not renamed the tier but it's not always by default the virtual sales. And of course then we could switch it. So this is simply in as I said before if you using one specific measure like for instance the average all the time that you wouldn't have to buther simply change it every time here. You could simply do it in the default section. Here are the four properties going here and then change the recognition method and the same is true. Basically for instance for the number formatting. OK. Because you could say for instance here for the soft sales I could go on here. It could go to four months and then please be aware that you do not want to form the excess year you wait for the pain. OK. So if it's a pain you've got the numbers here and then you could say I always want to see for instance the currency standard. Then you could see it. These are euros now because my local is Germany. You can switch of course to United States or NGO whatever you want. Or you could say currency custom then you could do much more formatting you. For instance I don't want to see decimal places can get rid of those. And I couldn't also say I want to see units for instance for thousands of both at a K place here and you can see you got 12 K instead of 12000 OK or 11000 and so on. All right. So these are things you can do it here was a person search depending on if you create any kind of peculation. For instance the growth rates calculations something that you could change it here or of course what's also possible go back back back. I could always go in here and can say by default I always want to see the sales as a Euro for instance and I could say here go to my 84 properties again I could say in this case year and I'm a formal thing and could say I always want to see it as a currency and that would see I want to see it as a U.S. dollar for instance. And then I also want to see it in a thousand case and don't add any decimal places that are going okay. And then if I double click on sales now then you can see these are my sales now. OK for k in dollars 3. And so on. Okay. These would be in this case the the aggregation. OK. And just by the way it looks a little strange because it's on the Simpsons right. We change the image. That's why this is the average but the formatting is still there's no new default formatting which we have applied now. OK. So these are just basic things. I want to you know at the beginning just to start with. And now let's get into calculating and using formulas within law. OK. So thanks a lot for watching and I hopefully see you as soon as possible in the next video. Until then I wish you all the best.