Tableau - Table Calculations

in this video we'll learn about the calculations part calculations are basically of two types in tableau the one first one is nothing but the database related calculations and second is pay bird related calculations database related calculations are directly related to your database where you can just calculate the difference percentage or you can say as minimum maximum from that place whereas there are second type of table related calculations are those which have been calculated based on the output in tableau suppose if you create some calculation in this place suppose if you draw some chart in this tableau itself based on the chart analysis if you calculate something that will come assess table related calculations with the help of example you will understand it much more better now the very first one which I can show you is percentage of total so what I've done is I've just basically brought this data that's a category here and sum of sales at this place I am getting the sum of sales total which is given as 12 6 4 2 5 0 2 that's the total which I've got if I want to calculate some percentage of this total which category is contributing how much percentage in this total itself so that is the topic that is percentage of total so whatever output I am getting in the tableau based on that if I am calculating something that is nothing but table related calculations so how did I bring this first of all I will just clear the sheet here pick up the category in the rows pick up the sales into the text part and there is no grand total here so I will go to this analysis and select as totals show column grand totals I am getting the column grand totals I will expand it so you can see better way like this yeah now I want to find out which category is giving how much percentage to this place so for calculating the percentage itself so I'll just click on this drop down and then I'll select this as quick table calculations and if you see there are these other quick table calculations which are given so if I select the option as percentage of total now I can easily understand which technology has which category has given the highest sales so technology is one thing which has given the highest sales among all of them the lowest sales is which are guarded from the office apply itself so that is nothing but the twenty nine point nine six that is thirty person is approximately now if I want to show this calculation in the form of a bar chart how do I do that so for that I'll just remove everything and then I'll select this option as category into the rows and then I can pick up the sales and drop into the columns part so if I want I can swap it like this on this place now if I just click on this T that is I'll be getting a sum of sales of all these options and if I expand it so that you can easily see that like this now if I want to see what is the percentage given by everything all this you can see as categories same method I'll just click on the stop down and I can select this option as quick table calculation and from this place I can select this option as percentage of total now with the help of bar graph I can tell you the highest percentage which is given by technology definitely I can see the help of this length of the bar graph but with help of this number I can tell you the exact that is thirty seven point five to nine that is the percentage which has been sold by this you know say technology it is the highest among all of them so that is nothing but known as percentage of total itself the next which I have the table calculations I can show you an example here now I will be showing you how to work on this place this is the sales based on the order date if you see that the sales based on the order date and these are the number of years 2012-2013 and so on here I am getting the numbers and Here I am getting the percentage of the total itself so here if you see the highest percentage was given in these sales was done in 2015 that was thirty two point nine six the lowest sales was done in the 2017 that was one point zero one so how do I create this kind of graph if you want to understand in detail what is the number exactly you can see that the number given the highest sales and the lowest sales which is given in number here how do I create this kind of chart itself the bar graph itself so first of all what I'll do is I will clear this calculation everything clear the entire sheet and first I'll become this order date and drop into the columns second I'll pick up the sales and drop into this rows part itself so I am getting this kind of line chart again I will pick up this scenes and drop into this rose I am picking up the sales two times so first I'll click on this automatic and convert into a bar chart itself so I've selected the bar now I've got the entire bar graph here top in the bottom both are one and the same I'll expand it so you can see it easily here like this at this place now if I want to give labels to all of them I'll just select this option as t that is the and select this option T here I'm getting the sales at this part Y because I'm getting it twice because I have selected this date major that is the sales measure twice in this place so therefore I'm getting two times this charge itself bar graph but the first one I want in the number of you knows the total sales and the second graph which I want in the percentage itself how much percentage which year it has contributed so for that what I'll do is I'll select the second one click on this drop down like this and I'll select the option as quick table calculations and I'll select as percentage of total so here I can easily get that's the percentage of all the total which is given here so easily I can find out the same thing thirty two point nine six here it is the total sales the lowest sales is one point zero one that isn't given here now if you want only this graph you don't want this one you can simply remove this and you can drop it outside but before before closing this what is the difference between the data based calculations and the table related calculations the difference I have shown you based on the output you get in the table if you calculate that that becomes as table related calculations any output you get in the tableau part so whatever output I've got based on that I'm just trying to calculate the difference you can understand with the help of this both looks simpler how do I find out which one is the table related and which one is database related the triangle says this is tabled related calculation so if you click on this drop-down click on this drop-down and if you see there's a quick table calculation which is given here so table related calculations will be which will be you know displayed in the form of a triangle whereas the normal calculations will will have no triangle itself so you can easily figure out if you want only this only this table related calculations in the charts and you don't want this one pick up this and you can just drop it outside anywhere and you'll get this kind of chart so this would be much more easier to represent and you know you can get an easy idea of this so here it is given as percentage of total sales versus the order date itself let's see one more if I want to find out the table calculations in this crosstab so if you want you can just convert this bar graph into a crosstab so how do you convert it right click on this place and select as duplicate as crosstab so when you click on duplicate as crosstab you get similar kind of table not the exact one because the fields are different here now in this place what I am trying to show you is I am trying to calculate the percentage of sales done by category and subcategory percentage of sales done right now I don't want the numbers figures I want a percentage so here if you observe I will just remove this heading which I don't want so I just click on this drop-down height title which I don't want here now if you see furniture is the category subcategory is given it says 11 point 88 is the percentage done for the entire grand total which is given as 100 person so here if you see the 32 percentage is given for furniture it is given as 20 nine point nine six for office supplies technology is giving us thirty seven point five three how do I calculate this kind of calculations this is table related calculations again so first of all I will show you how to create this kind of you know crosstab so I'll first remove everything from this place select a category and in the rows field select these subcategory and drop into this rows field again and then I will select this sales and drop into the text part this is somewhat similar to the first example which have shown percentage of total if remember there was a small you know crosstab which was given based on that we have seen I'll show you the same thing here itself now I am getting in the form of some numbers these are the total sales done so first I'll sort in the descending order like this one and I can see the highest sales done before the interest of the C and that is the chairs highest sales done in office supplies is about the storage and so on but I want in the in the form of a percentage same method you can just select this drop down and select the option as quick table calculation and you can select the option as percentage of total now you can easily figure out percentage of chairs is 11 point 88 if you combine all of them you will be getting as 100 person how do you get the grand total first of all go the analysis menu bar select the totals select the column grand total and you'll be getting the column grand total but now you are getting entire grand total what if you want the category wise subtotal so here if you want for furniture how much is the total office supply how much is the total you can go to this analysis totals and you can select this add all subtotals so if you observe here for furniture the entire total is how much is thirty two point five one so if we find out which technology is giving you the highest sales so I'll again close this high this title itself so which one is giving me the highest sales category wise if you observe the category wise higher sales is given by thirty seven that is the technology part now this is how you can work on this place so that is the nothing but table calculations in the crosstab so it's very simple if you want you can keep on you know adding some different details in this place right now if you don't want the category you know sales if you want to profit drop it outside become the profit and drop into the text and these are the profits which are done here if I say in the descending order and now I want the profit in the form of a percentage select quick table calculations percentage of total it says the highest profit done in this furniture is eleven person in office supplies nine person and technology.this seven person you can find out which is positive and negative by using this method distance so this is nothing but table calculations in the crosstab let's see one more how to differentiate how to differentiate between all of them with the help of this diagram I can show with the crosstab itself this one option named as running total what is this running total all about if I expand like this if I expand this place and if you see I'm getting the running sum of total and here it is sales what exactly is the difference first let me explain this these are the sales done by the month wise if you see month wise the sales are done now if I keep on adding month wise sales and if I want to find out the cumulative addition what are those so right now if you see that is eight one two five nine seven and if I combine this both sales if I combine this first and second I will be getting the one so if you see if I select this option and if I click on this place if I select both this one the total of this one is given here that is sum equal to one four one nine double nine zero and this is the sum which is given here if I combine three of them like this if I combine three of them calculation of sum of all these three is how much - four - four three nine five four and that is the calculations so rum-running sum of total means you keep on adding every month calculations and you'll be getting a graph itself so how do I calculate this kind of option here so this is one example so how do I create it first of all I'll just bring the options as measure names and the month which is given here click on this close clear the sheet first of all I will bring this option as let's say the order date order date into these columns and then I'll become this sales into this text part itself and I will just swipe this field yeah I'll just swipe this field itself now I'm getting the year wise the entire total but what if I want a month wise I'll just click on this plus sign and I'll remove this outside these are the quarter wise click on the plus sign again and this becomes in the month wise so I am getting month wise the sales but what if I want the cumulative edition of all these numbers become the sales and you can drop into the DOS part itself now what happens is if I pick up as it is in this place it is nothing but in the continuous format what I'll do is I'll try to change into a discrete format so just click on this drop down and I can select this option as convert to discrete so here if you see that's the same sales which I'm getting here now I don't want this kind of sales but what I want is I want cumulative sales that means if I add both of them I should get the Edition this both are same if you observe this both sales are right now same as it is how do I convert into a running total that means if I add both of them I should get the numbers here if I add three of them I should get the answer here so click on this drop down on this place and if you observe here there's a quick table calculation and here it is running total so I'll just click on this running total and if you observe I have got this total here so combining both of them I will be getting this answer if you combine all of them the last one you'll be getting this particular assets so this is how we can work on cumulative total let me explain you the same example with the help of a bar graph itself so first I'll create a bar graph here I'll press ctrl M and what are this I'll become this order priority let's say order date and drop into the columns select this sales and drop into the rows which I've got and I'll convert into a you can say as the bar graph again and second once again I'll become this sales and drop into this part itself select the sales and L drop into this rows itself now what is the how do I find out the running total how do I find out cumulative total this is the normal sales here wise they have given if you see that's a year by sales now I'll just select this option as P on this place and I will expand this so you can see that clearly yeah so these are the sales which are done now what I want is I want addition of all the sales I don't want like everything how it is given here I want to see every year how much is the sales done at this place so I want to keep on adding here how do I get the running total so if I select this option the second one that means the second graph which is given here select this option and if I say I want quick table calculations running total and if you observe the graph has changed what is it is trying to say if you are if you add this 2012 and 2013 if you add both of them you will be getting this answer if you add all three of them will be getting this answer so every year it is adding and final it is giving in the total sum final it is give me the total sum it is not visible because I have 2x point this size self so if you if you calculate this addition of this two numbers you will be getting this this number itself that means they have both added if you can combine this three you will be getting this particular Edition so this is nothing but running total or you can says cumulative total which you have got it with help of this bar graph itself now if I show you with help of this example one which I've got it here okay in this place I'll show you one more example here yeah I'll just create one more sheet here control m and with the help of this line chart I can show you how the accumulated total works so first of all I'll bring this order date into this columns and I will select this sales and drop into the row spot itself so I have got this you know sales here wise if you observe that's a year wise sales what I'll pick up this category and drop into this colors so these are category by sales which are then all the years now what I want is I want not the total I'll select this option from here and I'll select us I want us discrete format so this is the discrete format which I don't want so I'll select this drop-down and I'll select us continuous format which is perfect here in this place what I can say is these are the sales which is showing it is highest and lowest and then the lowest one I don't want I want the running total like this one which I've shown here I want the running total at this place so every year it should show me that it is going upwards right so how do I get it again I'll click on this drop-down select this as quick table calculations and they'll see us running total and if you observe that's a running total of all these heels total sales done will be given here and if I want to make in the form of another graph itself I'll just click on this drop down and if I select this option as area and if you see that's the area graph which is prepared here so this is how you can you know you can work on this table calculations using this tableau part itself so I hope you have understood these are the different examples in the table related calculations that's all for this video

Channel: Tutorials Point (India) Ltd.

Views: 225,101

Rating: 4.900806 out of 5

Keywords: Tableau - Table Calculations, Tableau, Table, Calculations

Id: 2f7Sl5WEExw

Length: 16min 32sec (992 seconds)

Published: Tue Jan 23 2018