When creating line charts I often want to be able to label the line ends AND also the min/max values, without over-cluttering the display by labelling every value.
As it stands* Tableau does not provide an easy solution for this - it's a case of one or the other:
Scouring these forums I was pleased to see this already raised as an Idea here https://community.tableau.com/ideas/1943. Please, spare a few seconds to give this feature the thumbs up for a future release; in the meantime, there are workarounds:
Solution 1 (Easy): Dual axis, synchronised
Very straightforward, assuming you only have one axis in play. Simply:
- Duplicate the measure (in this case, AVG(Discount) from the EU Superstore sample data set).
- Set the duplicate to appear on a Dual axis.
- Synchronise the axes so you are viewing the same chart overlaid on top of itself.
- Now you have two marks cards to play with; set one of these to label line ends and the other label min/max values. For the min/max, ensure you select the appropriate scope (in this case, Line/Pie because I want to see the min and max labelled for each line).
5. For maximum look and feel, change the colour on the first marks card to transparent. This makes the lines slightly sharper as Tableau does not EXACTLY render the lines on top of each other - there is a slight fuzziness. Just ensure you amend the first marks card to transparent, not the second, otherwise your line will disappear from view.
And there you have it:
Solution 2 (Harder): Table calculations
If dual axes are already being used for another purpose, we have to think again. In this example, I have already duplicated my axis to display an overall average, alongside the separate category averages. I can still label my first marks card but I am left with a choice between min/max or line ends:
This can be resolved using table calculations; specifically, four separate table calculations to test for the following:
- Is the value the highest/lowest in its category?
- Is the value from the earliest/latest year?
If any of these conditions are true then display the label, else display NULL.
This can be handled via a single calculated field with the following format (simply substitute your_measure and your_year for the appropriate fields):
IF MIN(your_measure) = WINDOW_MIN(MIN(your_measure)) THEN your_measure // checks for a min value
ELSEIF MAX(your_measure) = WINDOW_MAX(MAX(your_measure)) THEN your_measure // checks for a max value
ELSEIF MIN(your_year) = WINDOW_MIN(MIN(your_year)) THEN your_measure // checks for a line start (e.g. earliest year)
ELSEIF MAX(your_year) = WINDOW_MAX(MAX(your_year)) THEN your_measure // checks for a line end (e.g. latest year)
The above assumes your measure is not already aggregated; if it is (for example, AVG(Discount) as below) then you will need to lose the preceding MIN and MAX to avoid an error.
For example, I created a Discount min/max label field by substituting the values from my category example, simply by dragging the measures that are already in my view into the appropriate places in the calculation editor:
To check I am on the right lines, I added this to my view as an additional label. This confirms I am labelling the correct points but there is work to be done to format the labels (long decimals instead of percentages):
Formatting my field to one decimal place in the Pane tab gives me the following:
so I can now remove my redundant AVG(Discount) measure from the label to complete the view:
The attached workbook shows the two approaches.
This technique works even if your measure is also a table calculation - you just need to ensure each table calculation is working at the appropriate level, with the correct combinations of partitioning and addressing dimensions.
If there is an easier way of getting to the same result, please let me know.
*Admittedly I have not seen the v10 beta closely enough to know if this is resolved there - very happy to be corrected if this is already resolved!