2 Replies Latest reply on Oct 31, 2018 12:33 PM by Savannah Winstanley

    Multiple Measures represented in one X category

    Savannah Winstanley

      Apologies if this has a previous thread, I searched around a little and couldn't find the exact workflow solution I'm looking for.

      The end goal is to have a graph that shows the current/most recent value for a given lake in the same sheet as the maximum normal range, minimum normal range and the average for the quarter. I've looked at some workflows for doing this and the easiest to implement for me was to create a separate query table in the database source that did these calculations for me, and have it join to the data using the lake name and quarter. The closest I can get however is the third sheet in the attached workbook "Water Quality" which has these all on one chart. However, I'd like to have the distribution marks all associated with the same "Chla" column.

       

      In an extra ideal Tableau world I'd like to have this for Chla, TP, TN, and PCU, but I can live with having those as side by side charts if needed. I've attached a static image of the kind of chart I want to recreate.

       

      AVM_Mariana_2018_WQ.png

       

      Any tips & tricks would be much appreciated!

        • 1. Re: Multiple Measures represented in one X category
          swaroop.gantela

          Savannah,

           

          I didn't quite catch the gist, so my apologies if this is off base.

          Further apologies if you have already traversed this route and discounted it.

           

          It is my impression that you may be able to do the requisite calculations

          all in Tableau as Level of Detail calculations:

          Overview: Level of Detail Expressions - Tableau

           

          Not fully understanding layout of your datasources, I mocked up an example in Superstore.

           

          For example I summed the values over a quarter, and then found the Max quarter using:

          { FIXED [Sub-Category]:MAX(

          { FIXED [Sub-Category],[Order Date (Quarters)]:SUM([Sales] ) } ) }

           

          Likewise for Min.

           

          The average was a bit more involved. I took the averages of the quarters over the most recent (max) year:

          { FIXED [Sub-Category] , [Order Date (Years)] : AVG(

          { FIXED [Sub-Category] , [Order Date (Quarters)] : SUM( IF [Order Date (Years)] = { MAX( [Order Date (Years)] ) }

          THEN [Sales] END ) } ) }

           

          As you had done, these are all put on the Measure Values Shelf with Measure Names placed on the Color Shelf.

          The current value is on the dual axis.

           

          This may all be simpler in your true set, but just wanted to give an example.

          If completely off-base, will revisit in your workbook.

           

          Please see workbook v10.3 attached in the Forum Thread.

          Multiple Measures represented in one X category

          1 of 1 people found this helpful
          • 2. Re: Multiple Measures represented in one X category
            Savannah Winstanley

            Hi,

            Thank you for the response! - that example helped me think through how to do this in the future, once I wrap my head around how to reword those to work for my data. I think, just from looking at the workbook, that the nested if within the fixed expression will get me where I need to go.

             

            Before reading this, however, I went accidentally stumbled upon a lazier route and just got rid of the column shelf entirely which got me where I needed by duplicating that sheet for each of the 5 categories