I agree, it seems like this would be perfect for a Gantt Bar chart. We could have each measure supplying the start, and then have another calculated measure for the Size (length) of each bar that depends on the measure used, and be done. However, the view is created using Measure Names/Values, and Tableau does not allow us access to the individual measures within those convenience variables, so there is no way to create a proper set of values for the Size Shelf.
Like many situations in Tableau, though, there's a workaround. Gantt Bars are really just fancy versions of a stacked Bar chart. So we can create calculations to create the bars, and then use reference lines to identify the exact measures.
The messy part here is that in a stacked Bar chart, the measure value determines the length of the bar, with the prior value determining the start point (with a default of 0). When first measure is greater than 0 (Gross Margin in this case), we need to have a dummy bar to properly offset the start so it looks like a Gantt Bar, and when the last measure is <0 (Material Margin in this case), we also need a dummy bar to offset the end so it is less than 0. And since we're manually calculating lengths, we have to deal with different calculations between negative and positive numbers.
Therefore, to pull this off I created 7 calculated fields:
Negative Indicator - this has the formula
IF [Material Margin %] < 0 THEN
ELSEIF [Contribution Margin %] < 0 THEN
ELSEIF [Gross Margin %] < 0 THEN
It's a convenience field to use in other calculations so we don't need a lot of IF/THEN statements in the other 6 fields:
pre GM - does the blank filling from 0 to GM when GM is positive
post MM - does the blank filling from MM to 0 when MM is negative
-GM to CM - handles the -GM case
GM to CM - handles the +GM case
-CM to GM - handles the -CM case
CM to GM - handles the +CM case
The fields all return Null for cases they can't handle, so no extra bars are drawn. Note that the Measure Names are very specifically sorted to draw the lines properly, if they get out of order you're probably going to have play with the Color Shelf to figure out what goes where.
The calculations could probably be optimized to reduce the number of them, there would be three at a minimum (two for the bars and one for the spacer). Also, it could be possible to do this sort of thing using Custom SQL to perform the aggregations in the underlying query (I got the idea for this from an old Joe Mako post where he'd used Custom SQL to create a similar view).
In terms of cleaning up the view, the reference line formats could be changed to more clearly flag what they go with. The Color Shelf is a mess of variables, if I were using this in a dashboard I'd create my own legend and not use the Color Shelf.
Workbook is attached, let me know if this works for you!
"...Measure Names/Values ...Tableau does not allow us access to ... those convenience variables, so there is no way to create...."
So sadly true Jonathan.
This is the Tableau jackpot! The content you have given me is excellent and works very nicely! This is the exact look I was going for. I just didn’t know that it was going to be that complicated.
Would you have the link to the Joe Mako post you referenced? I’d be interested in taking a look at it to see how Custom SQL was used.
I appreciate your time and effort my man! Thanks!
Sorry, I don't have the link. If you search on Joe Mako and Custom SQL you might find it, you will certainly find other examples.
1 of 1 people found this helpful
Here is another option, use custom SQL to connect to the data, something like:
SELECT *, "CM %" AS [Measure] FROM [Sheet1$]
SELECT *, "GM %" AS [Measure] FROM [Sheet1$]
SELECT *, "MM %" AS [Measure] FROM [Sheet1$]
then create a calc field like:
WHEN "CM %" THEN sum([Cont $])/sum([Net Revenue])
WHEN "GM %" THEN sum([Gross Margin $])/sum([Net Revenue])
WHEN "MM %" THEN (sum([Net Revenue])-sum([Total Std Mat Costs]))/sum([Net Revenue])
I called it "Value"
Made a label field:
WHEN "GM %" THEN "GM to CM"
WHEN "CM %" THEN "CM to MM"
and a Length field:
setup the worksheet, sorting the Measure field, setting the compute using for Length to use Measure, filtering out when the Length is null.
see the attached.
That is also a very helpful approach. Once Jonathan mentioned Custom SQL it got my wheels turning. I was looking for some of your older posts that he mentioned, but I'm glad you found me! Thanks a ton for your help!