3 October 2014
In a change to the advertised blog I'm going to explore something slightly different while it's fresh in my mind - this will give me time to perfect Monday's blog post on the Dynamic Trellis.This week I was with a client who asked me to add Grand Totals to a Viz that looked like this (the below is an example and not the actual visualisation - but it was boxy! - let's leave the visual recriminations for another day and concentrate on the tip I'm about to go through):[tableau server='public.tableausoftware.com' workbook='LabellingTotals' view='TotalThis' tabs='' toolbar='' revert='' refresh='' width='600px' height='800px'] [/tableau]My first reaction was ?!!? how do we sum boxes?. It turned out all they were interested in was whether a particular action (in this case Order Priority) had happened for each Category, but then they also wanted to know in Total how many actions had happened overall that Month for each Category. Well fair enough, who am I to question why.So 'Challenge Accepted!'Of course my first step was to turn on the Column Grand Totals and add a Label using COUNTD([Order Priority]).
but that left me with dissatisfying 1's all over:[tableau server='public.tableausoftware.com' workbook='LabellingTotals' view='Withlabels' tabs='' toolbar='' revert='' refresh='' width='600px' height='800px'] [/tableau]Hmmm, I didn't like leaving it like that, so I started to experiment and came across a feature on the Label control I'd not used before:
I love learning new things about Tableau - this min / max feature was the answer to my prayers and I can't believe I've not seen it before - so of course I tried it out. The key was to work out which row was the Grand Total row - there are a few ways of doing this but I like using the formula:
and then this (notice i added my [?Show Total] pill to the level of Detail first):
And the result is below. Mission successful. Why does it work? Well my formula returns a 1 only in the Grand Total row, then I'm using the label settings to show the label only when my formula is a Maximum across the table, since it is zero everywhere except the Total Row then it only shows on the Total Row![tableau server='public.tableausoftware.com' workbook='LabellingTotals' view='WithTotal' tabs='' toolbar='' revert='' refresh='' width='600px' height='800px'] [/tableau]Personally this technique was new to me, I thought it was rather cool and worth a quick blog post, even if this use case is rather bizarre.
but that left me with dissatisfying 1's all over:[tableau server='public.tableausoftware.com' workbook='LabellingTotals' view='Withlabels' tabs='' toolbar='' revert='' refresh='' width='600px' height='800px'] [/tableau]Hmmm, I didn't like leaving it like that, so I started to experiment and came across a feature on the Label control I'd not used before:I love learning new things about Tableau - this min / max feature was the answer to my prayers and I can't believe I've not seen it before - so of course I tried it out. The key was to work out which row was the Grand Total row - there are a few ways of doing this but I like using the formula:MIN([CATEGORY]) <> MAX([CATEGORY])This is a neat trick because this expression is only ever False when the [Category] is in the Grand Total (NB if the Grand Total is only made up of one item you start running into problems as it will still be true).So next I did this:
and then this (notice i added my [?Show Total] pill to the level of Detail first):And the result is below. Mission successful. Why does it work? Well my formula returns a 1 only in the Grand Total row, then I'm using the label settings to show the label only when my formula is a Maximum across the table, since it is zero everywhere except the Total Row then it only shows on the Total Row![tableau server='public.tableausoftware.com' workbook='LabellingTotals' view='WithTotal' tabs='' toolbar='' revert='' refresh='' width='600px' height='800px'] [/tableau]Personally this technique was new to me, I thought it was rather cool and worth a quick blog post, even if this use case is rather bizarre.