I’m going to attempt to be really brief this week, as I have very little time to get this drafted before I head off on my holibobs for a couple of weeks (so I’ll be off grid for the next couple of #WorkoutWednesdays but will attempt to catch up when I return).

This week’s challenge was set by IronViz 2018 finalist, Corey Jones, and focuses on formatting a table – full challenge details here.

Presenting data in Tableau in an ‘out of the box’ table chart type, does not allow row level formatting in the manner described in this challenge (Tableau is not Excel you know :-))

So you have to be creative, and this is what this challenge is all about.

Corey gives the clue that no more than 3 sheets are allowed, hinting that the above presentation is not all on one view, but at least 2 carefully arranged side by side on a dashboard.

I had 3 views in my dashboard – the shaded table of sub-category and measures, the stacked bar of % of region sales vs rest, and the title.

Now I ended up with 3 including the title, as I used the [Region] field as a quick filter throughout my build, and so to enable the title to change dynamically I needed to create a sheet so I could reference that field. Having looked at Corey’s solution later on, he chose to use a parameter to drive the Region selection. Parameters can be referenced in dashboard titles, so in Corey’s solution, the Sub Category list on the left hand side is also a view, whereas I combined with the measures. The only thing I couldn’t achieve with the combination was being able to left-align the Sub-Category heading, while leaving the other headings (Sales, Profit etc) centre-aligned.

The formatted table

The magic ingredient in building formatted tables is using an axis with labels. In this instance the field MIN(1) is your best friend.

1. Add [Sub-Category] to rows
2. Type MIN(1) into columns
3. Set mark type to bar
4. Add SUM([Sales]) to the Text shelf and right align
5. Fix the axis from to start from 0 and ends at 1

and you get…

… something that looks pretty tabular, but you now have more flexibility with formatting styles.

This concept forms the basis for the whole table, but we need to step back quickly, as we need to set up some calculated fields.

Sales, Profit & Quantity are already defined in the dataset, but we also need

Profit Ratio

SUM([Profit]) / SUM(Sales)] formatted as a percentage to 0 decimal places

Total Sales for Year & Category

{FIXED YEAR([Order Date]), [Sub-Category]: SUM([Sales])}

This is a Level of Detail (LoD) calculation that stores the total sales for the year (2018) and subcategory, regardless of the [Region] filter being selected

% Sales for Selected Region

SUM([Sales])/SUM([Total Sales for Year & Category]) formatted as a percentage to 0 decimal places

Note – Technically this field will return % sales if any filter is applied, not just region

Highlight

[% Sales for Selected Region] > ([Highlight Threshold]/100)

This returns true where the % sales is more than the threshold selected by the user by entering a value into the [Highlight Threshold] parameter

The [Highlight] field can now be used to format the ‘table’ by adding to the Colour Shelf, setting true to be grey and false to be white.

Making the text bold

My solution differs from Corey’s. I chose to create the following calculated fields

LABEL : Sales Bold

IF [Highlight] THEN SUM([Sales]) END

This returns a value only against the rows where [Highlight] is true.

LABEL: Sales Normal

IF NOT([Highlight]) THEN SUM([Sales]) END

This returns a value only against the rows where [Highlight] is false.

Both these fields are placed side by side on the Label shelf, and with the ‘bold’ field formatted to be bold. As no row will ever have a value set in both fields at the same time, only a single value is displayed

Showing the column title at the top

I did this by creating an additional axis MIN(0), and applying dual axis which I synchronised. I removed all the pills from the MIN(0) marks card, and the [Measure Names] that had been automatically added to the colour shelf on the MIN(1) marks card.

This gave me an axis at the top which I could then edit – I changed the title to Sales, and set the axis tick marks to None for both major & minor ticks marks. On the bottom axis, I also had to edit, in this case setting the title to nothing as well as setting the axis ticks to none.

The height of the axis was also adjusted to make it appear closer and the font formatted accordingly. The Sub-Category was also set to not ‘Show Header’.

To create the other columns, the process is repeated with further dual axis MIN(1) & MIN(0) fields added to the columns, with further bold/normal Label fields also created.

The More Fields to the bottom left lists all the MIN(1) & MIN(0) cards in order.

To get the ‘column’ line to appear between the sub-category and sales columns, I set the transparency of the MIN(0) card associated to the Sales measure back to 100%, and it gave the appearance of a solid line.

Stacked Bar Chart

This is also a dual axis chart, which plots the % Sales for Selected Region field alongside our new friend MIN(1), where the MIN(1) axis is set to just over 1.

• The Measure Names colours are set to grey (% Sales) and white (MIN(1))
• The MIN(1) axis is set to the back
• % Sales for Selected Region is the label on the associated card, and left aligned
• A new field which is basically 1 – [ % Sales for Selected Region] is added to the label of the MIN(1) card and right aligned.
• A border is applied to the bars via the Colour shelf

To create the ‘column heading’ a new field is required

LABEL: Bar

[Region] + ‘ vs. All Other Regions’

This is then added to the column shelf to get

so when the Region filter changes, the title changes too.

Both these sheets are then positioned carefully side by side on a dashboard, setting the Fit to Entire View. The padding of the table sheet is adjusted so it has 0 outer padding on the right, whilst the padding of the stacked bar is adjusted so it has 0 outer padding on the left.

And finally, the Region filter is added to the dashboard, set to floating positioned just near to the heading of the stacked bar. It’s size is also adjusted so only the arrow part remains.

There’s a fair few other little formatting bits and bobs to get the text right, remove columns etc, but these are hopefully settings you already know about. Feel free to contact me if you can’t figure something out.

My solution is available here.

Happy vizzin!

I’m off for some sunshine 🙂

Donna