3 Replies Latest reply on Mar 15, 2012 10:24 AM by Jonathan Drummey

    Calculated Reference Lines based on Group,

    Scott Shaffer

      I would like to create a graph that shows the individual maximum transaction cost for a specific company. And also add a reference line that shows the maximum transaction cost for a group of companies. Right now I have a filter on company that makes it so the reference line max is only for the company that is filtered on. Is it possible to do this?

       

      Thanks!

        • 1. Re: Calculated Reference Lines based on Group,
          Jonathan Drummey

          Hi Scott,

           

          I just posted a packaged workbook showing how to do this using a duplicate data source here:

           

          http://community.tableau.com/message/174570#174570

           

          Jonathan

          1 of 1 people found this helpful
          • 2. Re: Calculated Reference Lines based on Group,
            Scott Shaffer

            Hi Jonathan, Thanks for the reply. That solution was very helpful, I think the issue that we are having relates to summing up rows of data and then doing calculations on the aggregated data. For example we have revenue at the transactional level and want to sum up that data to the company level... then find the max of those. And, hopefully compare that data, on the same chart, to the single company's data. I think we may have to change something with the way the data is set up.

             

            thanks,

            Scott

            • 3. Re: Calculated Reference Lines based on Group,
              Jonathan Drummey

              Hi Scott,

               

              Check out the attached. I'm not sure if this is exacly what you wanted, but it shows some of what table calculations can do in Tableau.

               

              To create this view using the Sample Coffee Chain DB, we simultaneously need to be showing the results of two calculations - one to show the Max Sales per Market (for a chosen Market or to show all Markets), the other showing the Max of that Max no matter what Market is chosen. Table calculations are a really powerful way to do this, since we can use the same transactional rows of data and do different calculations on them.

               

              In order to select a given Market and have only that Market display, instead of using the normal filters that would exclude data we need to perform the Max of Max Sales calculations, I created a parameter based on the values of Market.

               

              There are two table calculations here:

               

              Sales - Max:

              IF [Choose Market] = "All" THEN MAX([Sales])

              ELSEIF [Choose Market] = LOOKUP(ATTR([Market]),0) THEN MAX([Sales])

              END

              This calc returns a value for Max(Sales) for every row if All is chosen, otherwise if there's specific market selected it just returns the Max(Sales) for that market.

               

              TC Max Sales (across Markets)

              WINDOW_MAX(MAX([Sales]))

              This one is pretty straightforward.

               

              With both measures on the Rows shelf, and the TC Max Sales set to Dual Axis with Mark Type Line and the TC Max Sales (across Markets) axis set to Synchronize axis, we're getting close. The Sales - Max Marks have Color on the Market Shelf and are set to compute along Table Across.

               

              The tricky bit is that the TC Max Sales (across Markets) needs to have Market not on the Color shelf, but in the Level of Detail so we can get the Max of the Max of Sales per Market. Then the Compute Along needs have the Compute using set to Advanced, with Market, Quarter of Date, and Year of Date all in the Compute Using field. This way the calculation will truly find the Max of Max.

               

              screenshot.jpg

               

              If you had other levels of aggregation, those could be worked with as well. Let me know if this works for you!

               

              Jonathan

               

              Message was edited by: Jonathan Drummey I had a little brain freeze around the filtering when I created the workbook so I used the parameter-driven solution. Later I remembered that there's an easier, more dynamic way. I created a calculated field called Choose Market: LOOKUP(ATTR([Market]),0) And put that field on the Filters shelf.  Filters on table calculations (the Lookup) are performed after other calculations are completed so they don't change the results except in some really complex cases. I've updated the attached file with the new result.