6 Replies Latest reply on Jun 18, 2012 2:09 PM by Jason Krantz

    Using Gantt Chart To Display Margin Walk

    Jason Krantz

      I am trying to build a viz illustrating the magnitude of the margin walk that we have between our different customers. I have used a Gantt chart to successfully plot out the various margin points in our margin walk, but I am having trouble populating a color between the various points to illustrate the variable and fixed cost burden on overall margins.

       

      In the attached example I have a fictional customer that has 20.1% material margin (revenue - material costs), 7.8% contribution margin (revenue – (mat’l cost + variable costs)) and -1.9% gross margin (revenue – (mat’l cost + fixed and variable costs)).

       

      What I would like to do is the create a dimension that I can place on the “color” mark to fill the gaps between the various margin figures. For example, I’d like to fill the space between MM and CM with the color blue to reflect the impact of variable costs and I might want to fill the space between CM and GM with the color orange to represent the fixed costs. By doing this I can help mgmt see where margin drain is coming from.

       

      I’m sure the solution to this is simple, but I am struggling. Perhaps there is a better solution than a Gantt chart. Thanks in advance for your help!

        • 1. Re: Using Gantt Chart To Display Margin Walk
          Jonathan Drummey

          Hi Jason,

           

          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

          "-MM"

          ELSEIF [Contribution Margin %] < 0 THEN

          "-CM"

          ELSEIF [Gross Margin %] < 0 THEN

          "-GM"

          ELSE

          "+"

          END

          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!


          Jonathan

           

          • 2. Re: Using Gantt Chart To Display Margin Walk
            Shawn Wallwork

            "...Measure Names/Values ...Tableau does not allow us access to ... those convenience variables, so there is no way to create...."

             

            So sadly true Jonathan.

             

            --Shawn

            • 3. Re: Using Gantt Chart To Display Margin Walk
              Jason Krantz

              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!

              • 4. Re: Using Gantt Chart To Display Margin Walk
                Jonathan Drummey

                You're welcome!

                 

                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.

                 

                Jonathan

                • 5. Re: Using Gantt Chart To Display Margin Walk
                  Joe Mako

                  Here is another option, use custom SQL to connect to the data, something like:

                   

                  SELECT *, "CM %" AS [Measure] FROM [Sheet1$]

                  UNION ALL

                  SELECT *, "GM %" AS [Measure] FROM [Sheet1$]

                  UNION ALL

                  SELECT *, "MM %" AS [Measure] FROM [Sheet1$]

                   

                  then create a calc field like:

                   

                  CASE ATTR([Measure])

                  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])

                  END

                   

                  I called it "Value"

                   

                  Made a label field:

                   

                  CASE ATTR([Measure])

                  WHEN "GM %" THEN "GM to CM"

                  WHEN "CM %" THEN "CM to MM"

                  END

                   

                  and a Length field:

                   

                  LOOKUP([Value],1)-[Value]

                   

                  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.

                  1 of 1 people found this helpful
                  • 6. Re: Using Gantt Chart To Display Margin Walk
                    Jason Krantz

                    Joe,

                     

                    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!

                     

                    Jason