5 Replies Latest reply on May 9, 2017 1:47 PM by Jonathan Basirico

    Reference Lines by Cell for Different Measures?

    Diane Economides

      Is there a way to create reference lines as benchmarks for different measures within the same view?  I've attached a packaged workbook using the Sample - Superstore data source that has a bar chart using Sales, Profit and Quantity, along with some calculated fields I created with benchmark values for each measure.  How could I display a different reference line on each cell to show the benchmarks for each measure?

        • 1. Re: Reference Lines by Cell for Different Measures?
          Michel Caissie

          Diane,

           

          What you would need , is to be able to use the Dimension [MeasureNames] in an if or case statement, but it's not possible in Tableau.

           

          But what you can do is create a fake Measure Names Dimension. To do this, go in Excel, create a column with the cell values {Measure, Sales, Profit, Quantity},  copy the column  and paste it in a new worksheet. As you can see in the attached, it creates a new Datasource (clipboard_xxxx...). Next you can create two measures, one for the values and one for the benchmark

           

          Case ATTR( [Measures] )

          when 'Sales' then SUM([Orders (Sample - Superstore)].[Sales])

          when 'Profit' then SUM([Orders (Sample - Superstore)].[Profit])

          when 'Quantity' then SUM([Orders (Sample - Superstore)].[Quantity])

          end

           

          Case ATTR( [Measures] )

          when 'Sales' then 300000

          when 'Profit' then 30000

          when 'Quantity' then 100000

          end

           

          From there you can build you worksheet and use the benchmark to have one reference line per cell.

           

          Michel

          • 2. Re: Reference Lines by Cell for Different Measures?
            Diane Economides

            Thanks for the reply, Michel.  I tried the clipboard trick and that's very helpful for one-off cases, but I need to build something that's scalable and can be automated.  I was able to create a database table that mimics the clipboard data and connected to that data source to achieve the same results, but I would need to somehow join this data source with another data source that has other dimensions such as Gender in order to utilize a parameter that allows users to view all the data or slice the data by Gender.

             

            Using your edited workbook as an example for the concept above, is there a way that I could join the Clipboard_20160718T164118 data source with the Orders (Sample - Superstore) data source so I could pull the Region dimension from the Orders (Sample - Superstore) data source into Sheet 2?  I don't see a common field on which I could blend or join the data sources so I don't think this is possible in Tableau, but maybe so?

            • 3. Re: Reference Lines by Cell for Different Measures?
              Michel Caissie

              Unfortunately, the fake Measure_Names dimension cannot join in any way with another datasource. In the exemple I gave, the "join" is done directly in the calculation using the Case() function to determine which values of the secondary source is linked to wich value of the primary source dimension.

               

              Directly in Tableau, I don't see a way to accomplish what you are trying to do.

              There would be a  workaround  if you use customSQL datasource, but it would only be possible if your dataset is not to big,  and if you have only a couple of measures.

               

              For example, if your datasource was using a query like this (mysql syntax)

               

              select

              table.Category

              table.Region

              table.sales

              table.profit

              table.quantity

              from table

               

              you could replace it with

               

              select

              "Sales" as "fakeMeasureNames"

              table.Category

              table.Region

              table.sales

              table.profit

              table.quantity

              from table

               

              union all

               

              select

              "Profit" as "fakeMeasureNames"

              table.Category

              table.Region

              table.sales

              table.profit

              table.quantity

              from table

               

              union all

               

              select

              "Quantity" as "fakeMeasureNames"

              table.Category

              table.Region

              table.sales

              table.profit

              table.quantity

              from table

               

              Then create  a calculation for the Measure values like this

               

              Case fakeMeasureNames

              when "Sales" then sales

              when "Profit" then profit

              when "Quantity" then quantity

              end

               

              and one for the benchmark

               

              Case fakeMeasureNames

              when "Sales" then 300000

              when "Profit" then 3000

              when "Quantity" then 1000

              end

               

               

              So you would have a single datasource, a dimension to replace the Measure Names, and you could still use the other dimension to slice your bars.

              But again, it all depends on your business case. In this simple scenario, it triples the size of the datasource,  and it is error prone when doing aggregations.  On a worksheet where you don't use the fakeMeasureNames dimension with the computed values and benchmark, you would need to filter on the fakeMeasureNames  in order to aggregate on a single slice of the datasource otherwise you would get values 3 times the expected value.

               

              It's the best I can come up with, maybe someone else have a better approach...

               

              Michel

               

              • 4. Re: Reference Lines by Cell for Different Measures?
                Diane Economides

                Michel,

                 

                I really appreciate your insight and willingness to help find a solution.  That seems like it would work well with smaller data sets.  Our use case involves dynamically rendering a dashboard with the bar chart and benchmarks based on certain selections made by users, so we'll be wrapping the dashboard in a UI and the underlying data sources will change according to certain user selections.  Some of our data sources have tens of millions of records so most likely adding the union statements in the custom SQL query could negatively impact the dashboard performance, even if we use extracts and optimize the data sources to remove unnecessary fields, hide columns in the workbook, etc. 

                 

                I'm going to keep this post marked as unanswered in the event that someone has any other suggestions that could work with large data sets and our particular scenario.  Again, I appreciate your help and hopefully there might be some other way to accomplish what we're trying to do without too much overhead.

                 

                Thanks,

                Diane

                • 5. Re: Reference Lines by Cell for Different Measures?
                  Jonathan Basirico

                  I think that you could accomplish this by using a single measure and a single benchmark reference line. Then have a separate worksheet for each measure. Then add all of the measures to a dashboard. Not quite as elegant, but I have the same issue hence how I found this thread. This is what I have concluded that I need to do.