5 Replies Latest reply on May 21, 2017 1:31 PM by Okechukwu Ossai

    Identifying primary attribute based on max measure value

    Laurie Paciocco

      I wrote and re-wrote the title on this, so please bear with me while I try to explain...

       

      I have a time entry dataset that includes developer, development team, client, and time, and I want to identify which development team has the most time logged against each client.  While I have a solid understanding of data structures and can be pretty scrappy in Excel, I'm only about two months along in my journey in Tableau.

       

      Can someone help me understand the best way to use Tableau in terms of calculated fields, measures, dimensions, attributes, etc. to address?

       

      Here's how I would do it in Excel (still has a manual component, so not very scalable)...

       

      Please help!

        • 1. Re: Identifying primary attribute based on max measure value
          Justin Larson

          Well, Laurie, you're jumping right into the advanced stuff for only two months in!

           

          Take a look at attached mockup. I used superstore sales and the aggregate AVG([Sales]) to simulate your hours figure, and Category as proxy for Client, and Segment for Team.

           

          I dropped in AVG([Sales]), then made a copy for reference sake, but the original measurement could be taken out. On the duplicate, clicked the pill, selected Quick Table Calculation>Rank, then clicked it again>Compute Using>Segment (which would be Team in your example).

           

          You can see this ranks the values shown for each Category. You can drop this calculation in filter, and keep only those for Rank = 1.

           

          As long as you leave Category and Segment (Client and Team) on the view, this calculation will work no matter how you arrange the pills. If you drop in a new dimension, things will get complicated, because that changes the level of detail for the sheet, and the result of the Rank formula.

           

          Let me know if that does enough, or if you need to take it a step further. (Window calculations take practice. I would suggest you start by go watching a bunch of the training videos to start getting your head around them.)

          • 2. Re: Identifying primary attribute based on max measure value
            Laurie Paciocco

            This is amazing!  Heading out for the weekend, but I can't wait to dive

            into this on Monday morning!

             

            Laurie Paciocco

            804.357.4546

            ProfitOptics, Inc.

             

            On Fri, May 19, 2017 at 5:17 PM, Justin Larson <tableaucommunity@tableau.com

            • 3. Re: Identifying primary attribute based on max measure value
              Okechukwu Ossai

              Hi Laurie,

               

              This is an alternative solution that recreates what you did in Excel like for like using LOD expression. This approach is very good because [Primary Team] is an actual field which can be used in tables, charts and calculations. Also [Primary Team] is self-contained, so you don't need to have Client or Team in the view to make it work. Also changing the level of detail by removing or adding new fields will not affect your result.

               

              [Primary Team]

              { FIXED [Client]: MAX(IF { FIXED [Client]: MAX({ FIXED [Client], [Team]: SUM([Hours])})} = { FIXED [Client],[Team]: SUM([Hours])}

              THEN [Team] END)}

              This formula looks complicated at first glance especially as you are starting out with Tableau. However, it is very easy when you look at it in smaller bits. The formula is exactly what you did in the yellow and green table in Excel. FIXED is Tableau's keyword for Group By. So, in summary the formula looks at each client and compares the total hours for each Team and returns the Team with the maximum number of hours.

               

              See attached workbook in Tableau 9.3.

              Hope this helps.

              Ossai

               

               

               

              • 4. Re: Identifying primary attribute based on max measure value
                Laurie Paciocco

                Wow, thank you so much!  Agree - the formula looked a little overwhelming

                at first, but it makes total sense once decomposed (if team's hours on a

                given project = the most hours by any one team on that project, then that's

                the one!).  Thank you for explaining the "Fixed" as Group By - I'm pretty

                sure you just saved me hours and hours of trial and error.

                 

                Laurie Paciocco

                804.357.4546

                ProfitOptics, Inc.

                 

                On Sat, May 20, 2017 at 8:56 PM, Okechukwu Ossai <

                • 5. Re: Identifying primary attribute based on max measure value
                  Okechukwu Ossai

                  You got it! That's exactly what the formula is doing.