2 Replies Latest reply on Jul 6, 2012 1:27 PM by Anil Malhotra

# Create % calculation using dynamic denominator

I need to calculate the percentage of time a particular seat location in a sports stadium is occupied by a paying customer given certain filter conditions.  For each seat location this requires two numbers:

Numerator:  count of the number of "paying customers" for each seat location.

Denominator: number of games as selected by the user

My challenge is that the denominator is driven by a filter that enables the user to select the specific games. For example if the total universe of games available in the database is 30 but the user only selects 20 games, then the % calculation needs to be based off of 20 games and not 30.

Just starting out with tableau and not familiar with how to solve this calculation.

Thanks

Anil

• ###### 1. Re: Create % calculation using dynamic denominator

Hi Anil,

The denominator would probably be comprised of a calculated field similar to the following:

total(Count([Number of Games]))

The total function is a table calculation--meaning that it takes into account only what is in the view. Therefore, it will only count the number of games that are selected by the user. The following link provides more information on different function of table calculations and how they work:

http://onlinehelp.tableausoftware.com/current/pro/online/en-us/calculations_tablecalculations.html

Hope this helps!

-Tracy

• ###### 2. Re: Create % calculation using dynamic denominator

Hi Tracy,

Your suggestion was incredibly helpful.  The calculation worked well when I applied it to a table of seat locations.  My next step is to get it to work in a heat map.  I've been able to create a custom x,y map of the stadium in Tableau. I'm also able to map a simple statistic such as "avg paid amount" by dropping it on the color shelf and getting a gradient of colors to represent the amount people are paying.

When I put this new paid capacity calculation on the color shelf it doesn't offer the same results.  I'm basically looking to create a gradient of colors on the heat map which represent the avg paid capacity for each seat given the filter selection. Able to do it for avg paid amount but not with the new paid capacity calculation.

I am able to get something to work when I convert the measure to "discrete" but then I lose the ability to have an automatic gradient of colors and instead get a unique color for each "paid capacity" point. Is there any work around for this?

Any thoughts?

Thank you!