The Total function in Tableau & partitions

0:00Hey it's Tim here in today's video I'm

0:02going to be covering something called the

0:03total function

0:04in Tableau. Now in order to explain this

0:06properly I'm also going to need to cover

0:08another concept

0:09called a partition in Tableau. If you're

0:11not familiar with that don't worry I'll

0:13explain it

0:13in this video let's get stuck in. Okay so

0:15we're here in a very simple view I'm just

0:17going to go

0:17to the product hierarchy drag subcategory

0:20onto rows and sales onto abc to build a

0:24very simple

0:24table. Okay essentially this table just

0:27shows the total for each subcategory. Now I

0:30'm going to go

0:31ahead to straight and show you the total

0:32function remember in my previous videos I

0:34've been talking

0:35about how you get a description for each

0:37function here in this little window if you

0:39can't find it

0:40just hit this little arrow here and it will

0:42expand that pane to the right. Let's go in

0:45here and type

0:45total and you'll see that it's right here

0:47so you can see the definition here returns

0:50the total for

0:51the given expression. Okay I actually don't

0:53think this description is clear enough

0:55because if you

0:56if you've used maths in general in other

0:58tools the term sum and total can sometimes

1:01be used in the

1:02same context so you're probably wondering

1:04well what's the difference with the sum

1:05function or

1:06what's the difference between totals and

1:08sums. Okay let me show you so in the view

1:10right now

1:10we already actually have a sum you can see

1:12here that it's essentially just totaling up

1:15all the

1:15cells within each subcategory. So what does

1:17a total do? Well let's go ahead and type

1:19the

1:20function let's step in total here and when

1:22I double click that it automatically

1:24completes

1:24that let me just make this larger so you

1:26can see what's going on a little bit more

1:27clearly

1:28and then what I'm going to do is I'm going

1:30to hold command control on a windows

1:32machine

1:33and drag that into the brackets and that

1:35will put that in there so now we have our

1:37total function

1:38around the sum of cells the sum of cells in

1:41this case is our expression. Okay so let's

1:44go ahead

1:44and call this total and let's hit apply and

1:47you'll see that that actually applies here

1:50on the left

1:51hand side so I'm going to drag it in put it

1:53inside of the same box here and it has them

1:56side by side

1:56so what's actually going on well if you

1:59look at this let me just click okay here

2:00and just expand

2:01this a little bit more you'll see that it

2:04has the same value all the way down this is

2:06essentially

2:07because what the total function is doing is

2:10it's looking at this table and adding

2:12everything up.

2:13Now you might wonder why is it adding

2:15everything up well it's because this whole

2:17table represents

2:19a single partition okay let me change

2:21things up a little bit and you'll see how

2:23this number changes

2:25as I change the chart okay let me this time

2:28go and drag category in front of sub

2:30category

2:31and you'll notice the number stays the same

2:34nothing changes okay and that's because the

2:36definition of the partition hasn't changed

2:38yet because when we built the chart the

2:41definition

2:41of the partition was using this terminology

2:44here at the top that says table down okay

2:47so essentially

2:48it's looking at this entire table and

2:50looking down the chart and it's going to

2:52compute everything up

2:54until this little section in fact if I

2:55bring up my annotation tools let me just

2:57show you this

2:58I'm just going to draw a square here and

3:00this is essentially what it's doing it's

3:02looking at this

3:02whole table and totaling everything up in

3:05this direction okay and it's essentially

3:08doing it for

3:08this column but then it's resulting in here

3:10and then once it has the answer it repeats

3:13that value

3:13in every single cell so it sort of does the

3:16calculation then returns the value in every

3:18single cell okay still with me all right I

3:21know this is getting confusing very quickly

3:23but just

3:24keep with me for a few more minutes and we

3:26'll get there okay now what I'm going to do

3:28is I'm going

3:28to change the definition of this partition

3:30so you can see what's going on what I'm

3:32going to do is

3:33change it to something called pane down

3:35okay I'll just explain what a pane is in a

3:37second but let me

3:38just show you what happens let me click on

3:40this triangle here on the pill and if I go

3:43to compute

3:43using you'll see that I get this sort of

3:46table of options I actually can't annotate

3:48it whilst it's

3:48open unfortunately so I'll just keep it

3:50open and I'll just explain it here you see

3:52table down

3:53pane down pane across then down pane down

3:56then across these get very confusing very

3:59quickly

3:59I'm actually going to do a separate video

4:01on this very very soon but if I just select

4:03pane down

4:04notice what happens to the number you see

4:07it immediately changes okay and that is

4:10because

4:10the definition of the partition is now

4:12being controlled by something called the

4:15pain in this

4:15particular case a pain refers to something

4:19like this so this is essentially a pain

4:22okay and the

4:23whole entire table if I just change the

4:26color is something like that okay so green

4:29is a single

4:30pane and blue is the entire table so this

4:32is actually what's changing we're changing

4:34the

4:35definition of how it's doing the

4:36calculation let me just go ahead and clear

4:39that and so now we can

4:40actually validate that this is doing what

4:42we think it does because if I just hold

4:44shift here just

4:45select each of these you'll see that table

4:47does a nice little sum there 742 000 if I

4:50click back out

4:52and you'll see that that is what that

4:54number is there now if I go in here and

4:56actually just select

4:56office supplies you'll see that it does a

4:59total of these numbers in here and actually

5:02sort of does

5:02a really high number that's not what we

5:04want we just want each of these in here so

5:06let me just go

5:07ahead I could have actually control drag

5:09that but you'll see 719 000 and it's just

5:12there I'm holding

5:13shift as I select those and that's how it's

5:15staying there and tublo is doing some maths

5:17for me okay

5:18now what I could also do is I could also go

5:21to the worksheet options and then show the

5:24summary

5:25the summary is essentially a pane on the

5:27right hand side that tells us a set of

5:28values the reason

5:29I want to do this is because I'm now going

5:31to change the computations and mix things

5:33up a little

5:34bit we know what the total function does it

5:36adds everything up then repeats that value

5:39across every

5:39cell we now understand what partitions is

5:42we're basically describing the way in which

5:44table looks

5:45at our table table down looks at the whole

5:48table pane looks at each individual pane

5:51and essentially

5:51to have a pane you have to have some sort

5:53of hierarchy so if you've got two pills

5:56here in the

5:56row shelf you have a single pane in your

5:59table now if I was to add another thing

6:01into the pill shelf

6:02the definitions of the pane would actually

6:04become even smaller let me show you if I

6:06just grab order

6:07date and put that in front of measure names

6:10what ends up happening is the definition of

6:12a pane

6:13now includes the year so now this 157

6:16includes the values just for 2017 because

6:20my pain is essentially

6:22this little square I've just activated the

6:25annotation here whilst I was on the box if

6:28I do

6:28this outside and then draw this box here

6:30you'll see that that is a single pane so

6:32the definition

6:33of a pain always stays the same visually in

6:36tableau it's essentially a sort of a fixed

6:38definition but all we're really doing when

6:41we tell tableau which way to look at the

6:43calculation is

6:44describing the direction of travel for that

6:46calculation and notice now that I've added

6:49another pill into the view this list of

6:51items has gotten longer don't worry too

6:53much about this when

6:55I've done the video on this topic you'll

6:56see it flashing on the screen as a tool tip

6:58to go and

6:58watch but for now just understand that the

7:01total will look at the context of the table

7:03and depending

7:04on what partition you select table down

7:07pane down pane across it will use that as a

7:09way of calculating

7:11what's going on okay so that's pretty much

7:13the you know summary of the total function

7:16it takes

7:16everything into context and returns a

7:19single value across every cell and you're

7:21going to need a good

7:22understanding of a partition to really take

7:24advantage of it and really make the most

7:26use of it

7:27now we've used some of cells here but the

7:29total function of course can work with

7:31other functions

7:32let's edit this calculation and see what

7:34happens okay so let's go ahead and actually

7:37open our total

7:38function here click edit and you'll see

7:40here that we have some of cells I'm

7:42actually going to change

7:43this to average cells okay so what I'd love

7:45to ask you is what do you think it's going

7:47to do in this

7:47particular case previously it did the sum

7:50so in this particular case what you'd

7:53expect is it does

7:54the average for the partition okay and so

7:57that actually requires going down into the

8:00data getting

8:00everything but it's not just going to get

8:03the values that we see let me just show you

8:05what I

8:05mean let's hit apply and you'll see that

8:08the number for this top left pane is now

8:11373 okay

8:13now this is 2017 furniture okay this is

8:16essentially what the average is okay and

8:19the way it's doing

8:20that isn't by looking at this let me just

8:21change this actually to an average let me

8:24change this to

8:24an average so we can see what's going on

8:27okay let's just remove that bracket and

8:30yeah enter

8:31there we go it's not getting this by adding

8:33these up and then calculating the average

8:36in fact if I

8:36do that if I click on that hold that hold

8:39that and hold that you'll see the average

8:41over here

8:42in the summary pane is 466 this is actually

8:45a very common issue when people are

8:47building charts with

8:48averages they forget to understand that the

8:51average in tableau tableau will actually go

8:54back into the data set and look at every

8:57single record inside of that partition and

8:59then calculate

9:00the average that way okay so the average

9:03sale for bookcases might be 4 542 the

9:06average for chairs

9:07might be 599 the average for furnishings

9:10might be 75 the average for tables might be

9:13649 but you

9:14don't you don't get an average for

9:15furniture by adding those four things then

9:18dividing it by four

9:19you actually get every single record in

9:21furniture and you calculate the average

9:23across every single

9:24record and that actually returns a

9:26different average 373 probably because our

9:29furnishings

9:30don't sell that highly so this average is

9:32being dragged right down but we can sort of

9:35see the

9:35effect of that actually if we just exclude

9:37that you'll see that now it shoots up to 6

9:3905

9:39okay so what i did there is i just right

9:42clicked on the item and then excluded okay

9:45and if i go

9:45back one step and just click on it again

9:48you'll see that we go back to where we were

9:50before so

9:51the total function will do whatever

9:53expression you're telling it to do across

9:56the whole entire

9:57partition okay so this is probably one of

9:59the simplest functions with probably the

10:02most

10:03difficult to explain concepts because it

10:05sort of straddles two things partitions and

10:08the direction

10:08of table calculations which again i've said

10:11i'll do in another video but also just this

10:13concept that

10:14the term total can be used to work with

10:16something like averages which doesn't sort

10:18of make natural

10:19sense but that's that's pretty much how it

10:21works okay so think of what the function

10:23does rather

10:24than what it's called and you'll be flying

10:26with this function okay that's it for today

10:29i'm deliberately avoiding a lot of pitfalls

10:31for this one i think i'm gonna have to come

10:33back to

10:33each individual concept over time but i'm

10:35deliberately trying to keep these videos

10:38brief and to the point so each function

10:40gets its own dedicated explainer i

10:42definitely encourage

10:43you to go out there and read a bunch of

10:45blogs from the tableau community about the

10:47term total

10:48you'll see lots of different opinions and

10:50and thoughts about this specifically when

10:53when it

10:53comes to things like even performance

10:54because you can do quite a few things

10:56differently in

10:57tableau and one of those is to try and

10:58optimize the way certain calculations are

11:01done and total

11:01is a very useful way of thinking about

11:03certain things in certain situations okay

11:05that's it for

11:07today if you've enjoyed this video you know

11:08what to do if you haven't leave me a

11:09comment below let

11:10me know what you'd like to see next i'm

11:12working my way through all these functions

11:13that are most

11:14commonly used in tableau this is going to

11:16be part of a playlist so look out for that

11:18playlist i'm

11:19going to link it in the description when it

11:20's ready otherwise i'll catch you in the

11:22next one