5 Replies Latest reply on Sep 2, 2016 1:58 AM by Simon Runc

    Creating Average Value of a Measure across Multiple Dimensions

    Adam Hirschberg

      I'm working on an exercise where I have the following information:

      • List of Airline companies
      • Data on # of destinations served, size of fleet, 2013 passengers, etc.
      • 3 set competitors for each company (note: these are independent--just because Delta has American as a competitor doesn't mean American has Delta in its competitor set)


      My goal is to create a "Competitive Set Average" of the # of passengers, which updates based on a parameter selection for each team. That is to say, if I use my parameter to select American Airlines, I want the "Competitive Set Average" to pull the average of total passengers for each of American's competitor airlines (which are, in this case: Delta, United and Alaska).


      Any ideas on how to best do this? Appreciate any help/insight!

        • 1. Re: Creating Average Value of a Measure across Multiple Dimensions
          Simon Runc

          hi Adam,


          ...Interesting challenge!...and hopefully you'll find my solution equally interesting!


          So I've gone a bit off-piste with LoDs here. So the key to it is this formula

          IIF([Airline] = [Parameters].[Airline Selection],[Competitor 1],NULL)


          which I've left in (for Competitor 1) to show you what it does...I then use a FIXED LoD to populate all rows, with competitor 1, with the MAX...the MAX, or MIN for that matter, of NULL and something is always the something! (even for strings). Also note as FIXED LoDs these are all row level calcs, so work regardless of your VizLoD.


          {MAX(IIF([Airline] = [Parameters].[Airline Selection],[Competitor 1],NULL))}


          I then do this for each competitor...and then I can use this in the following statement


          IF [Airline] = [Parameters].[Airline Selection] THEN 'Selected'

          ELSEIF [Airline] = [Competitor 1 - for Selection] OR [Airline] = [Competitor 2 - for Selection] or

              [Airline] = [Competitor 3 - for Selection] THEN 'Competitor' ELSE 'Exclude' END


          and so we assign each Airline with 'Selected', 'Competitor' or 'Exclude'...hopefully you can take it from here


          Let me know if that doesn't make sense.

          2 of 2 people found this helpful
          • 2. Re: Creating Average Value of a Measure across Multiple Dimensions
            Yuriy Fal



            Another option could be using joins at the datasource.

            The bonus is having Averages for all Airlines in a table.


            Please find the attached.




            1 of 1 people found this helpful
            • 3. Re: Creating Average Value of a Measure across Multiple Dimensions
              Adam Hirschberg

              Hi Simon,


              Thanks so much for your thoughtful reply - this is definitely helpful, and I can create an average of the 3 airlines in each competitive set.


              One of the things I'd like to do with this average is build it into the visualization from sheet one. I've pasted an example of the chart I'm trying to build below (image from an excel dashboard I built with the same data).

              Sample Chart.png


              Do you know if its possible to display the average of # passengers for airlines in the "Use These Airlines" dimension you created within the Sheet 1 tab? I'm trying to figure out a way to do this without creating separate sheets and combining them in a dashboard. Of course, I'd like the chart to update based on parameter selection (which should be taken care of).


              Appreciate any insight you have on how to do this, and thanks again for your help!!

              • 4. Re: Creating Average Value of a Measure across Multiple Dimensions
                Adam Hirschberg

                Thanks for your help, Yuriy!


                I like your approach a lot, will be replicating this later on a significantly larger data file with many more measures and dimensions--having that all in the data tab to the left of my workbook might make it a bit overwhelming for the audience I'm submitting this too.


                That being said, I think your approach is very smart. Any thoughts on how I might be able to structure the YF Average measure you built into a chart similar to what's on Sheet 1? As mentioned in my reply above, I'm looking to put this all into a single chart on a single sheet, which looks like this:

                Sample Chart.png


                Let me know if you have any recommendations on how to do this! Right now my only approach is to put the chart form Sheet 1 into a dashboard alongside another sheet that displays a single bar for the competitor average... this doesn't feel like the best solution, but it's all I've come up with at the moment. Any recommendations on how to combine things all into one visualization similar to the pasted image above?


                Thanks so much for your help!

                • 5. Re: Creating Average Value of a Measure across Multiple Dimensions
                  Simon Runc

                  Yuri, That's a really clever use of Joins...I'll be using that one (the reason I knew how/why to use my LoD solution is that we have a similar problem, from a legacy data-structure...in the tool we used before Tableau, there was a very nice Tolkenise Filter option, so if you have a single field with say "Hot Beverage, Caffeine, Coffee" [a CDT] we were able to make a drop down filter with these 3 elements to filter on...very cool, but you can do end up with some ugly data structure...when we moved to Tableau I had to use SPLIT...and then this trick to do a similar thing)...I like the Joins one much more...and is far more efficient!


                  Hi Adam,


                  So once we have these are 'proper' row level dimensions, we can use a mixture of filtering (out the 'Exclude'), and this dimension to create the Viz.


                  In the attached, I've created 2 examples...in the first I created an ordering, very similar to the final calculation before (just break it out into 4 options, not 3)


                  [Use these Airlines - Order]

                  IF [Airline] = [Parameters].[Airline Selection] THEN 1

                  ELSEIF [Airline] = [Competitor 1 - for Selection] THEN 2

                  ELSEIF [Airline] = [Competitor 2 - for Selection] THEN 3

                  ELSEIF [Airline] = [Competitor 3 - for Selection] THEN 4



                  I can then use these to layout the Viz...for the variance formula, although this looks quite scary, I just made it using the 'Quick Table Calc' on %age difference, and then changed the 'relative to' to first (this is where the ordering is also useful)



                  I then just dragged the new (Tableau created) field into the measures, and renamed it, and added the extra logic so we wouldn't get a mark for the Selected Airline.


                  The logic for the 'Single Worksheet_Industry' is similar.


                  Hope this helps get you started...to have both Airline level, and overall in a single sheet will be tricky (not impossible...I think!!), but that's what dashboards are for...so you can move between grains of data with ease!!