4 Replies Latest reply on Jul 28, 2015 1:29 PM by matthew.eisenberg

    Calculating New Field W/ Filtered Data




      Everyone here has been a tremendous help with each question I have previously had, I figured why not come back here with my next question. 


      The following is what I am currently working on: https://public.tableau.com/views/MatchupComparison/Sheet4?:embed=y&:display_count=yes&:showTabs=y


      The Team filter is currently set to show two teams that are selected by a parameter using the condition [Team]=[Team Select #1:]or[Team]=[Team Select #2"]. 


      Team Pythag is a calculated field on its own that works with Offensive and Defensive Efficiency across a single row. 


      What I am trying to do next is a create a calculated field for "Expected Win Percentage".  This would take bits of data from both Row 1 and Row 2.  Looking around, it would appear that "lookup" and "first" is the best way to do this, correct?


      Expected Win % = (Team Pythag A - Team Pythag A * Team Pythag B) / (Team Pythag A + Team Pythag B - 2*Team Pythag A*Team Pythag B).


      Any assistance or advice would be greatly appreciated.




        • 1. Re: Calculating New Field W/ Filtered Data



          You can try:


          (LOOKUP(ATTR([Team Pythag]),0) - (LOOKUP(ATTR([Team Pythag]),0)*LOOKUP(ATTR([Team Pythag]),1))) /

          ( (LOOKUP(ATTR([Team Pythag]),0)+

             LOOKUP(ATTR([Team Pythag]),1)-

             (2*LOOKUP(ATTR([Team Pythag]),0)*LOOKUP(ATTR([Team Pythag]),1))))


          With a "Compute using" of 'Team'

          • 2. Re: Calculating New Field W/ Filtered Data

            Thank you for this formula.


            It produces an outcome for one of the two teams.  Would there be then a way to produce a figure for the second team as well?


            This concept of looking up attributes is definitely new to me and I apologize for what might be simple tasks I am overlooking for the time being. 


            Further down the line, there are other calculations where I hope to take part of row 1 and part of row 2 as well.  While the calculation of win percentage above has a total of 100%, the future calculations I hope to use will not have any base total or sum.  For example, I hope to create a "predicted offensive efficiency" total for each team.  This will include multiplying team A offensive efficiency by team B defensive efficiency by a national average (TBD figure). 

            • 3. Re: Calculating New Field W/ Filtered Data



              No problem about the ATTR, was only using that because the LOOKUP required some kind of aggregation.


              Is the calculation for Team B equal to 1-Team A?

              If not, you can use the same formula and just change all the

              LOOKUP(ATTR([Team Pythag]),1) to

              LOOKUP(ATTR([Team Pythag]),-1)

              which will lookup the value in the top line.

              Then you can make one Calculated field

              IF INDEX()==1 THEN Expected Win % A

              ELSE Expected Win % B


              So with this approach, you would need to make two calculated fields for every metric,

              one for Team A and one for Team B, and then one more piece to say if Team A then

              show the metric for Team A, etc.

              There are likely more efficient ways to do this.

              1 of 1 people found this helpful
              • 4. Re: Calculating New Field W/ Filtered Data

                That helps, thank you.


                Yes, in the case of win% team B would be =1-Team A