How to Display the Top N and Retain the % of Total

for this week's tablet tip Tuesday I'm going to show you how to display just the top end records but retain the percent of total for just those top end it's a little bit tough for me to describe this so let me actually show it to you in an example so let's say that we want to look at maybe regions by subcategory and we just want to do a simple bar chart of sales and what we want to do is we want to show the top five within each region and we want to label the bars by the percent of total okay so to label the bars by percent of total is pretty simple so and it takes sales drag it to the labels and then I'm going to make this a quick I'm going to add a table calculation and I'm going to make a percent of total product subcategory and hit OK now you can see when I less oh just this first set of records here in the central region you'll see it adds up to a hundred percent okay so the next thing that I need to do is I need to be able to filter the top five so I'm going to do that through a simple ranked calculation so I'm going to click on Jeremy up here in the dimensions area and click on create calculated field I'm just going to call it my ranked sales and the formula is pretty simple it's just the rank of the sum of sales hit OK and I'm going to convert that to discrete because I know that I want it on my road shelf and I want it to be a blue pill so I'm going to convert it to discrete and I'm going to stick it between my region and product subcategory because this the sales rank will now help me with my sorting so I have to edit this table calculation for my sales rank and we're going to go into the advanced window set my addressing to my the hierarchy in my view which is region and product subcategory and then I wanted to restart every region so I hit OK and now you can see I've got 1 through 17 here 1 through 17 etc all the way down now what I want to do is I want to keep the top 5 within each region so to do that and notice how now I've got this nice little ranking of my of my of my bars and they add up to a hundred percent let me go ahead and format my percentages make them a little bit cleaner so I'll make them maybe maybe one percent something like that and if I want yeah I'll just leave it as say as I'll just leave my bars as sales and then when I hover over you can see both the sales and the percent of sales so that's good okay so now what I want to need to do is I want to allow my user to decide how many products sub-headers they want to view so to do that I'm going to click on Jeremy again and I'm going to do create parameter I'm just going to call it my top n I'm going to make the data type integer and then just hit maybe I'll to fault it to let's say 5 and hit OK and then I'm going to right click on the parameter and choose show parameter control and you'll see now I've got 5 I can type 10 I can type any number I want in there so what I need to do now is I need to create a calculation that's going to basically compare my sales rank to this parameter so again I go back to Jeremy click on create calculated field and I'm going to say rank to show and I'm just going to say the rank of sales is less than or equal to my top n and this is going to result in a boolean calculation then we're going to drag that to the filter shelf choose the true option and I should see just my top 5 ok so from here I need to edit my table calculation and I just want to make sure that my sales rank is still computing the same way and it is so I'm just going to hit OK from this point I can uncheck my header for my sales rank and you'll notice how these numbers do not add up to 100% now they add up to it looks like what 10 about about 50% something like that so my top 5 only make up 50% so it didn't recalculate these two just the percent of total for those 5 records it's still maintaining the percent of total for the overall region and that's what I was looking for in this example so from here I would go ahead and I would format my view and reduce my Road my row dividers so I can get a little cleaner look here I'm going to switch to my favorite font which is a veneer and bold and unbold everything to make them all nod bold and from that point you know you could show the the sales rank if you want so maybe I'll turn that back on but this time maybe I will format these and I'm going to go ahead and make the format a custom number with no decimals but I'm going to put a dot on the end and then I'm going to right align them so now you can see I've got a nice little ranking of my of my my sales and I like how it has like a one dot you know maybe I could go ahead and if I hide the field labels it hides that for all of them so I don't really want to do that so maybe I'll just rename my rank to just my I'll just call it rank instead okay so now I can make this a little bit thinner something like that and we get a nice little it looks like they belong together one through five so now my users can come in here and they can maybe pick the top 15 and you'll see it goes 1 through 15 or they can pick maybe they only interested in the top 3 whatever example they want they can now get to so again the idea here was to be able to retain the percentage so right now you see I'm looking at my top 3 this looks like it's a 30 about 38 39 % make up the the top 3 within the central region and I didn't want this to recalculate to just you know about you know what 40 30 and 20% something like that I just wanted it to retain the overall the overall percent of total so I hope that helps and if you have any quote any questions feel free to let me know thank you very much

Channel: Andy Kriebel

Views: 77,769

Keywords: tableau, tip, rank, top n, filter, table calculation, % of total, parameter

Id: KnqLQsEzb0A

Length: 6min 24sec (384 seconds)

Published: Tue Mar 01 2016