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

# Calculating New Field W/ Filtered Data

Hello,

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.

Thanks,

Matt

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

Matthew,

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

Matthew,

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

END

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