4 Replies Latest reply on May 5, 2011 8:44 AM by Orien Mcglamery

    Calculate Percent of a Table Value

    Orien Mcglamery

      I want to calculate the percent of the cohort total that was retaied each year. 

       

      The file contains one record per student for each term enrolled.  The Cohot Total is the sum of all records that have a value of 0 in the Years_retained field (the base year).

       

      I have provided an example of what I have and what I want in the attachment.

       

      Orien

        • 1. Re: Calculate Percent of a Table Value
          Joe Mako

          How about something like the attached?

          • 2. Re: Calculate Percent of a Table Value
            Orien Mcglamery

            Wow, that worked!  Can you explain the formula to me?

            • 3. Re: Calculate Percent of a Table Value
              Joe Mako

              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.

               

              The

              LOOKUP()
              function allows you to get the value from else where in the crosstab, in this case I am getting the
              FIRST()


               

              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

              • 4. Re: Calculate Percent of a Table Value
                Orien Mcglamery

                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!