Multiple Measures on Both Sides of Dual Axis Using SQL

Version 2

    Description:

    Tableau currently allows you to place multiple measures on one axis of a chart, but you can only place one measure on the second axis of a dual axis chart (i.e. there aren't two Measure Values\Names shelves). You can overcome this limitation by using a Custom SQL connection.

    Example Connection:

    SELECT *,

    "Shipping Costs" as [Type]

    FROM [Orders$]

    UNION

    SELECT *,

    "Time To Ship" as [Type]

    FROM [Orders$]

    UNION

    SELECT *,

    "Unit Price" as [Type]

    FROM [Orders$]

     

    Comments:

    By creating three connections using [Type] to differentiate the three measures of interested, we can then set up a viz that allows multiple measures on one axis, and these three measures on the other axis. Here's an example:

    Dual Measures.png

    Caution #1: If you have large datasets or one with lots of fields, you may need/want to limit what fields you duplicate. Here is an alternative way of doing the same connection as above with few fields being brought in:

    SELECT

      [Orders$].[Order Date] AS [Order Date],

      [Orders$].[Profit] AS [Profit],

      [Orders$].[Sales] AS [Sales],

      [Orders$].[Ship Date] AS [Ship Date],

      [Orders$].[Shipping Cost] AS [Shipping Cost],

      [Orders$].[Unit Price] AS [Unit Price],

    "Shipping Costs" AS [Type]

    FROM [Orders$]

    UNION

    SELECT

      [Orders$].[Order Date] AS [Order Date],

      [Orders$].[Profit] AS [Profit],

      [Orders$].[Sales] AS [Sales],

      [Orders$].[Ship Date] AS [Ship Date],

      [Orders$].[Shipping Cost] AS [Shipping Cost],

      [Orders$].[Unit Price] AS [Unit Price],

    "Time to Ship" AS [Type]

    FROM [Orders$]

    UNION

    SELECT

      [Orders$].[Order Date] AS [Order Date],

      [Orders$].[Profit] AS [Profit],

      [Orders$].[Sales] AS [Sales],

      [Orders$].[Ship Date] AS [Ship Date],

      [Orders$].[Shipping Cost] AS [Shipping Cost],

      [Orders$].[Unit Price] AS [Unit Price],

    "Unit Price" AS [Type]

    FROM [Orders$]


    So instead of using the wildcard '*' that brings in everything, this code limits the data to only the fields we will be using in this particular visualization.

    Caution #2: When you create a viz using this strategy, there is potential of having your data multiplied by the number of times you brought it in. This will happen if you don't use the Type field on both axises of the chart. For instance in the attached file the right axis uses the Type field to color the lines, so the data comes from the three different connections. However the Measure Values on the left axis do not use the Type field so the data will get multiplied three times (in this case). To solve this problem use a calculation to limit the aggregation to a single connection. For instance:

     

    IF [Type]='Shipping Costs' THEN [Profit] END

     

    This needs to be done for each measure on the Measure Values shelf.