How to Display KPIs Next to Bars

in this week's tablet tip Tuesday I'm going to show you how to display KPIs next to bars I presented this as the data school gym challenge and ultimately what we want to end up is with something like this this view here where you have the arrow that indicates that there is a problem next to the bar but the issue is we want it to always be the same distance from the bar previously I'd written something a blog post about how to use ASCII indicators but that's not really what we want to do because those require you to put them in the in the rows as separate separate separate dimensions so I'm going to show you first I'll introduce you to the problem and then I'll show you the solution so I'm connecting to superstore sales and the first thing I'm going to do is I'm going to drag order date out into the view and let's maybe just start with a view that's customer and let's just look at sales for order date something like that now what I want to do is I want to use the last two years so what I'm going to do is I'm going to create a calculated field that is I'll call it my current year or let's call it latest year and this is going to simply be a little calculation says if the max I'm going to use a level of detailed calculation here the year of order date is equal to the Year of order date then give me the sales and I don't know I'm sorry else zero okay let's see and it looks like it doesn't like that so oh I forgot the end of my level of detail expression so let me stick that in here okay so that's good so now if we take latest year and we put that in the view instead you'll see we just get 2015 perfect so what I'm going to do is I'm now going to duplicate that and I'm going to edit it and I'm going to call it previous year or prior year in this case I'm going to say if the max of year of order date minus one is equal to the Year of order date then sales good okay and what I want to do now is I want to take prior year and put that in the view all right so now you can see we get this nice we get things lined up nice and neat okay so let's go ahead and take year off the view let's take measure names and measure values off okay so we just have customer segments and what I'm going to do is I'm going to create a bar chart that shows my latest year sales I'm going to drag prior year to the detail shelf go to the analytics pane and add a reference line and what I'm going to do is I'm going to show a reference line for the prior year for every row I'm going to make it black and I'm going to turn off the recalculation okay so now you can see it looks like consumer is down versus prior year whereas corporate is above excellent okay so now what I want to do is I want to create a calculated field that calculates the difference between the two really all I want to all I care about is is it above or below the previous year so I'm just going to call this a year over year and in this case I'm just going to say the sum of latest year is greater than or equal to the sum of the prior year hit OK and I get a year-over-year metric and what I want to do now is I need to actually I want to go ahead and create one more calculated field and for this I'm just going to call it my let's call it mark position and I'm going to start by just making it the average of zero and let's say we want to go backwards you know fifty thousand they're actually so I'm going to need to do zero I could just do minus fifty thousand and hit OK and I'm going to put that in the view as well and then make it a dual axis okay and then synchronize and now if I go to my latest year I'm going to switch this back to a bar go to my all marks card remove measure names okay and then on my mark position what I want to do is I want this to be a shape and I want to put my year over year on the shape so for here I'm double-click my shapes and the ones that are false I'm going to go ahead and I'm going to make those down arrows hit OK and then I'm going to right click on my true and go ahead and hide those because I don't really care to see those and then I'm also going to drag my year-over-year to color because I want those to be red okay so now I can drag the size a little bit make it a little bit bigger all right something like that from here I would probably go ahead and uncheck the show header and turn off the tooltip for that mark okay so now we can see you know whether it's up or down but the issue now is if I start adding dimensions so consider the white space between the arrow and the bars if I add something like region you'll see I now get a much bigger gap what I really want to solve is I always want the gap to be the same size so in other words it should always be the same distance from the left edge of the bar so what I'm going to do is I'm going to go ahead and loops I'm going to go ahead and create a and actually I've got one more issue I think I need to solve here so let me put subcategory here that's not the one I'm looking for so there is an instance let me see if I can find it here okay but anyway you can see how the problem continues to get worse and worse so what we need to do is we need to make this dynamic so I'm going to just go ahead and duplicate this sheet because we're going to leave that one alone and I'm going to create another calculated field and this time I'm going to call it I'll call this one alert position and really what I want to say is I want to say if my I want to say if let's say if my I could do my year over year so that's going to return true so if that is true then I want to return my so in other words if that's bigger than I want to return my sum of actually I just want to return the sum of let's see latest latest year else I want to return the sum of prior year okay and end and this is going to basically return that value so let's see what that does so far so let's let's replace market position with alert position okay so you see how that kind of places it out there so it's not quite what we're looking for so what I actually want to do is I want to take the window max of this so I want to do the window max and then just wrap this whole thing in a window max all right and hit apply and you'll see once I hit okay and make this table down you'll see I get that mark all the way out here on this on this bar which is good but what I really want is I want it to be to the left of the bar so let's just continue to play with this so I'm just going to stick a negative in front of that and if I hit apply you'll see it goes way out here to the end but really wanted what I want to do is I want to maybe multiply that by maybe 0.05 something like that hit apply and now you'll see it's really it's nicely positioned just to the left of the bar so hit OK and now as I swap out my metrics you can see it always is the same position from so I kept a product container in here maybe move product container to left and my arrow is always positioned in the same spot maybe throw a customer segment out there you see how it always maintains it in the right spot so it's a good use of table calculations there maybe I want to use something like let's try I've got customer segments I got product container product category so maybe let's stick category in here but one of the issues I'm going to find if I can possibly find it is let me take Container off there is an issue at some point where we don't have anything for our year-over-year sales maybe if I put ship mode out here so really what you should be doing is in my your year calculation I really should wrap this in AZN because I want this to always return at zero if it's a null value in other words if I don't have sales for one of the years just convert it to zero so sorry wasn't able to find that example but anyway so that's the way that I would handle this I would go ahead and again UNTAC and then choose not to show the header and now you've got these nice little these nice little indicators right next to the bars and if you don't like the position of it you can always edit the calculation and maybe you want it to be instead of 0.05 you want it to be maybe you know 0.02 or something like that you want to move it a little bit closer to the bar so you know you'll sort of figure out what works for you but but yeah I think this looks a lot nicer now and so hopefully you found that useful and I'll be back next week with another tip have a good day

Channel: Andy Kriebel

Views: 146,826

Rating: 4.9494948 out of 5

Keywords: tableau, tip, KPIs, table calculation

Id: OUTi12VtXpU

Length: 9min 45sec (585 seconds)

Published: Tue Jun 28 2016