3 Replies Latest reply on Feb 29, 2012 5:46 AM by Jonathan Drummey

    Adding grand total to graph showing regions

    Chris von Rabenau

      So, I have not found the answer to the question digging around in the KB, forum or videos. Maybe I am missing something.

       

      Reference information, I am reasonably new to Tableau, running 7.0 Desktop.

       

      I have a pile of sales and customer count data. I am calculating average transactions size with a calculated field:

       

      sum([sales])/sum([cust_count])

       

      I am graphing the average of this field by region.

       

      Screen Shot 2012-02-27 at 9.55.57 AM.png

      I would like to include a line for the entire sector, which is would be all regions combined. How do I do this? I have several uses for this (gross sales, gross customer count, etc.). So far haven't tackled this one.

       

      Thanks, in advance.

        • 1. Re: Adding grand total to graph showing regions
          Jonathan Drummey

          Here you go:

           

          1. Ctrl-drag your Trans Size field on the Rows shelf to the right and release. There will now be two charts that look the same.

          2. Click on the 2nd Trans Size pill on the Rows shelf and change the Mark Type to Line. The Marks card will now show < AGG(Trans Size) >.

          3. Drag the corridor pill off the Color shelf. The bottom chart will now have only one line.

          4. Click on the 2nd Trans Size pill again and select Dual Axis. The two charts will now be back to one, with an extra line.

          5. Right-click on the right-hand axis and select Synchronize Axis to make sure the axes use the same scale.

          6. Use the Marks card to appropriately format each set of line(s). You can click on the < > to switch between sets of marks.

           

          Once you've done this, you can do things with that 2nd Trans Size pill like use the Quick Table Calculation to change it to a Moving Average, etc.

           

          Jonathan

          • 2. Re: Adding grand total to graph showing regions
            Chris von Rabenau

            This is nearly perfect!

             

            Is there a way to throw a label it and place in a legend?

            • 3. Re: Adding grand total to graph showing regions
              Jonathan Drummey

              To create a legend, you have a couple of options:

               

              1. Create a legend in a graphics program, then create a dashboard with your view and the legend graphic.

               

              2.Create a calculated field (I called it "Transaction Size") with just a text value "Average Size". Put that on the Color shelf for the Trans Size mark, and an additional color card will be added with that legend.

               

              There's a third way you can sometimes get to by reshaping your data and using measure values so that both the regions and average are all part of the same dimension, the first two options are generally simpler.

               

              For a label, you can use a mark label to annotate the line. I used the "Transaction Size" value, put that on the Label shelf, and set Tableau to show mark labels on line ends, and just the end of the line.

               

              Also, in Tableau 7 on dual-axis charts you can hide the 2nd header, so I did that as well in the attached workbook.

               

              Jonathan