Luke kicked off the 1st workout of 2020 with this challenge – using a ‘header’ sheet to control the sorting on a tabular view below.

Building the table

A couple of measures need creating initially :

Sales / Order

SUM([Sales])/COUNTD([Order ID])

Profit Ratio

SUM([Profit])/SUM([Sales])

The table consists of 3 measures aligned side by side, of different mark types : bar, text, bar. Sub-Category exists on the Rows.

The Sales measure is displayed first on the Columns, with mark type set to Bar, and the axis is fixed to start at 0 so there is minimal spacing between the Row label and the start of the bar. An additional calculated field [Profit Ratio]< 0 is added to the colour shelf and adjusted to be red/grey as appropriate.

As the requirement for the text Sales/Order measure was to right align it, adding Sales/Order measure to columns won’t work. Instead use MIN(0) setting the mark type to Gantt Bar, and making the mark type as small as possible, and setting the opacity to 0. Sales/Order is then added as a label which is then aligned middle left, and therefore right-aligning the text on the screen.

The final measure, Profit Ratio is added to the Columns shelf, also as mark type of Bar. Once again the [Profit Ratio]< 0 is added to the colour shelf.

All row/column lines, gridlines, zero lines and axis lines were set to nothing, but the Profit Ratio measure required a zero line to display. Using the zero line setting of the formatting would have meant a zero line would also display on the Sales bar chart, which wasn’t required. Instead I used a constant reference line of 0 on the Profit Ratio axis only.

And finally, the requirement stated that sort controls on the table should be switched off. This setting can be found under the Worksheet menu. All the axis were then hidden.

Building the ‘Header’ sheet (basic)

To start off I’m just going to describe how to construct a basic header table so it can then it interact with the table on the dashboard, and apply the sorting. I’ll revisit this all later, as getting the header to meet the specific requirements Luke stated was something I admit I struggled with.

At it’s simplest, you can build the header as follows:

• Measure Names on rows and filtered to just the 3 measures we need
• Measure Names on Text and aligned Middle Centre
• Measure Values on Detail
• Measure Names manually sorted in the order we want
• Show Header unchecked for Measure Names on the rows shelf

Invoking the Sort

The sort is intended to work by clicking on a label on the header sheet, which in turn should sort the data in the table sheet.

To start off, a string parameter is required which is defaulted to the value Sales

A calculated field is also needed to define the sort measure, based on the sort parameter

Sort By

IF [Sort] = ‘Sales’ THEN SUM([Sales])
ELSEIF [Sort] = ‘Profit Ratio’ THEN [Profit Ratio]
ELSEIF [Sort] = ‘Sales / Order’ THEN [Sales / Order]
END

On the Table sheet, the Sub-Category field is then set to sort based on the Sort By field

With the Header sheet and the Table sheet added to a dashboard, a Parameter Action can be created which will set the Sort parameter based on the measure name clicked on :

At this point now, you should have the basics of the challenge – a header sheet causing the data in the table sheet to sort based on the label clicked on.

However, Luke added additional complexity to the Header sheet :

• add an ▼ to identify the column label clicked on
• change the font to be darker for the label clicked on
• ensure the column labels not clicked on, remained ‘visible’ (ie did not appear to fade as per standard behaviour when data is selected in a viz).

Building the Header (Initial attempt)

My initial attempt to meet the stated requirements made use of a dual axis MIN(0) table, where one axis was of mark type square, with the Measure Names & ‘ ▼ ‘ on the label shelf, which was formatted to be a dark font, and set to only show when highlighted. The second axis was a Text mark type, with Measure Names on the text, formatted to a lighter font.

On click on one of the labels, the label associated with the square mark type would be shown, but the other labels ‘faded out’.

Whatever I tried to do to stop them, based on tricks I’ve tried in the past (using a Dummy field and applying a highlight action on the dashboard), didn’t work.

As much as I tried I couldn’t get beyond this, so published this as my solution, and waited for Luke to release his workbook. My version using this method is here.

Building the Header (Luke’s method)

Once Luke made his workbook available, I downloaded it to see what he’d done to keep all the header labels ‘enabled’ on click.

He used a dashboard action filter to set a calculated field ‘True’ to match a calculated field ‘False’. Both these calculated fields had to exist on the Detail shelf of the header sheet. (also note how the dashboard source sheet selects the Header v2 view which targets the Header v2 sheet directly (and not the equivalent view on the dashboard).

This is a technique Luke has blogged about on his own site following a discussion in the Tableau Community : https://www.tessellationtech.io/automatically-deselect-marks/

I have to admit, I had seen this post, and am kicking myself I didn’t remember it to try it out 😦

So…. back to my viz, and I applied Luke’s deselect marks idea to my header sheet, but it didn’t work. As I’d made the arrow show on ‘highlight’, this filter action was undoing the highlight, so preventing my label to show.

So I figured I’d just have to copy what Luke had done… and I started to build out his sheet… 3 columns of Min(0) set to Text with Measure Names on the Text :

All I could display was Min(0)… the only way I could see to get the words Sales, Sales/Orders, Profit Ratio displayed, was to add in Measure Values to the Detail shelf… but then all the words overlapped…

So I messaged Luke, as I just couldn’t understand this black magic… but before I got a response, I had a lightbulb moment….

Aliases!

The MIN(0) in the Measure Names had been aliased, to look like the same name of other measures, but I added a leading and trailing space around them all, as you can’t have duplicated names.

This really is something sneaky! but I was quite chuffed I found it before Luke told me.

To get the display and the formatting right, then required an ‘Arrow’ field for each measure along the lines of :

Arrow for Sales

IF [Sort] = ” Sales “
THEN “▼”
ELSE ” “
END

(note the leading & trailing spaces). This field is added to the Text shelf on the 1st Min(0) marks card. An equivalent field is then required for the other 2 measures, which are added to their respective marks cards.

Sort – Sales

[Sort] = ” Sales “

This boolean field is then added to the Colour shelf of the 1st Min(0) marks card. Again an equivalent is required for the other 2 measures. Setting the colours correctly will require each label to be clicked on the dashboard to get all the true/false permutations to show.

My revised version to meet the requirement is available here. Be aware if you do download, the field naming might not be identical to that above. This is because I have 1 workbook with both solutions in it, so in some cases needed to have duplicated versions of the calculated fields/parameters, to ensure both versions continued to function.

That was certainly an interesting challenge for Week 1 of the new year, and ultimately I was stumped! Hoping this isn’t going to be a theme for the weeks to come 🙂

Happy vizzin’!

Donna