Wow, that worked! Can you explain the formula to me?
the formula for the custom table calc I used is:
SUM([Number of Records]) / LOOKUP(SUM([Number of Records]), FIRST())
and a description of each part:
SUM([Number of Records])is the count of records for each cell in the crosstab (the count of records for each distinct combination of your dimensions), this is the numbers that you displayed in your image.
LOOKUP()function allows you to get the value from else where in the crosstab, in this case I am getting the
Then as you described, I divided the number of records for each cell by the number of records for the first cell in that row.
I ensure that happens correctly by setting the partitioning (at this point there are many different setups in partitioning that produce the same results). I prefer to set my partitioning as statically as possible so I can rearrange pills on the worksheet, and not have to worry about changing the partitioning to be based on my new layout of pills. In each sheet, I am using the same partitioning with a different layout, but there are other ways to set the partitioning to get the same results.
When editing the formula for the calculated field, there is an option in the upper right of the dialog for "Default Table Calculation", and from this dialog I set the Compute using to "Advanced". I moved "YEARS_RETAINED" from the list on the left to the list on the right, and set the Order Along to "YEARS_RETAINED" "Average" and Ascending.
These setting ensure that the LOOKUP() function will get the fist "YEARS_RETAINED" value, the value when "YEARS_RETAINED" is 0.
In the crosstab sheet, you could also simply set the Compute using to "Table (Across)", instead of Advanced, and get the same results, but you would have to change the partitioning if you changed the layout of the pills on the sheet.
I could talk for hours about all the different options you have available to you with table calculations. Generally, if there is a calculation that you want performed, it can be performed in Tableau.
Here is a good resource for seeing what is possible with table calculations: http://www.tableausoftware.com/table-calculations
Thank you very much for the detailed explanation as well as the link. This is my first project using Tableau and I am pretty stoked right now!