4 Replies Latest reply on Jan 17, 2013 2:49 PM by Jason Borucki

    Plot an average in addition to individual series

    Jason Borucki

      I am looking to one of more series in addition to an average based on that time period.  I would like Tableau to calculate the average based on the filters, but display individual lines with the average.  An example in Excel is attached, though in that example the average is calculated (I am looking for Tableau to do this so if only "Red" and "Green" are in the filter then the average is for those two series).

       

      Thanks,

      Jason

        • 1. Re: Plot an average in addition to individual series
          Joshua Milligan

          Jason,

           

          Tableau can do this fairly easily.  I used the data you had in Excel, but did not include the Average records as Tableau will do this as an aggregation of the data (so it will work no matter what is filtered).

           

          I used a dual axis for the average value.  If you are not familiar with dual axes, you'll want to check this out: http://kb.tableausoftware.com/articles/knowledgebase/Secondary-Axes

           

          I also had a small calculation to convert the year to a date so Tableau would be able to draw connected lines.  Finally, I added a parameter that just had the constant "Average" so I could label the average line -- that's not essential to the solution at all.

           

          Hope this helps!

           

          Joshua

           

           

          average.png

          • 2. Re: Plot an average in addition to individual series
            Jason Borucki

            Thanks Joshua.  Perhaps this is an obvious question, if so my apologies.  I think the part I am missing from your example is how you show the average across all the series on one axis, but the sum for individual series on the other axis.  I understand the dual axis, but am having trouble showing individual members in combination with the average across series.  I can either show six lines (sum and avg for RGB) or two lines (sum and avg for total).

             

            Thanks,

            Jason

            • 3. Re: Plot an average in addition to individual series
              Joshua Milligan

              Jason,

               

              I'm not entirely certain what the issue is, but I suspect that on the second axis (for average) you may not have the series removed from color.  You can specify multiple mark types for dual axes which will allow you to manipulate the marks shelf independently for each axis.  In this case, you will have the same mark type (a line), but you'll have different levels of detail (color defined by Series for the SUM and not split for the AVG).

               

              You can get multiple mark types from the small drop down arrow on the Marks shelf.  Then you can use the <  and > buttons to switch between the axes (see attached image)

               

              If you're still having issues, please post your packaged workbook and I'd be happy to take a look!

               

               

               

              Joshua

              1 of 1 people found this helpful
              • 4. Re: Plot an average in addition to individual series
                Jason Borucki

                Joshua, everything is looking as expected now.

                 

                Thanks for your help!

                 

                Jason