This week’s challenge was pretty simple in the wider context of Workout Wednesday, but still presented a few challenges for me to overcome. A link to Andy Kriebel‘s original blog post is here, and my final attempt can be viewed and downloaded here.
Challenge number one is to write a calc to work out the distinct number of regions that each product is sold in. That’s simple enough, even more so when you remember that Andy shared a video last week looking at Level of Detail calculations, which included this very scenario:
It transpires that how I created the treemap varies to Andy’s method. I used Show Me but placed different stuff on the various Marks Cards. Having created the calc above, I then duplicated it and converted it to a Dimension. My treemap structure is:
Note that the ATTR(Region(s)) calc is just to make my tooltip work out:
Andy did this to create his treemap:
So [Regions per Product] is the same as my [Products per Region] calc, which I had duplicated and converted to a Dimension as that’s how I figured I’d get discrete colouring. Whilst my tree map is sized by the original calculation, Andy’s [Sort] is:
My method works best for the way my brain operates, but the end result is the same. That’s as complicated at the first step goes. One semi-sneaky inclusion is the oft-overlooked use of Captions on the worksheet, to create the chart footer:
You add these via the Worksheet menu (Show Caption). Then the text of whatever you enter is shown on the worksheet you add to the dashboard, but the Caption header is not retained.
The final pieces of the puzzle come in the form of the final summary header section:
This posed me with two challenges, which were overcome as follows. Firstly, how do we show how many products were sold in the 1, 2, 3 and 4 Region Categories? Don’t we just use the existing LoD calculation?:
Now I know I used the same data source as Andy, so why does my 1 Region total match Andy’s, but why is my 2 Region total twice that of Andy’s? Why is my 3 Region total three times that of Andy’s? Why is my 4 Region total four times that of Andy’s? Hold on….my totals are being multiplied by the Region count – easy enough to fix with another calculation:
In contrast, Kriebs used a really simple calc which basically amounts to the same thing:
This works, because Andy’s view is already broken up on Columns by the Dimension version of my original calc. Cool. So we have the count of distinct products sorted, and my earlier [Region(s)] calc covers the final part of the view. What about that great big percentage at the top? This had me unnecessarily stumped for a while, and then I just tried a table calc:
Just a basic percent of total standard table calc, leveraging my badly named [Products per region 2] calculation, which is outlined an image or three ago. Once you have the separate parts of the view in place, you can start to structure the worksheet:
And some final formatting on the Text card tidies the label up:
The scene has been set – in a couple of weeks when Andy follows this up, he has promised a proper monster of a challenge, which took his decade of Tableau experience a couple of days to overcome. Enjoy the easier #WorkoutWednesday weeks, because when they get tough, it gets painful!