3 Replies Latest reply on Sep 30, 2016 6:54 PM by Ivan Young

    Calculated fields across table rows

    Amanda Lamb

      New user here, so please forgive incorrect terminology. Also, please forgive any race names that might not be the most PC. This is the data I am working with, and I will be improving labels in the final version.

       

      I am trying to create multiple calculated fields in a table and having no success. Here is the table:

       

       

      What I need is:

      • Field 1: Calculate the # accepted cases / grand total for each row. (Result: the case acceptance rate for each race)
      • Field 2: Divide the results from Field one for black and Hispanic by the white. So for each row Field 1(black)/ Field 1(white) (Result: the relative rate (RRI) of acceptance for other races, compared to white.)

       

      Here is what that final result would look like this:

      Finally and for the actual dashboard, I would want to chart Field 2 (RRI), which would look like this:

       

      This seems like it should be simple (it is in Excel), but I've spent a long time on it at this point and can't make what I am trying work. Help!

       

      Thanks,

      Amanda

        • 1. Re: Calculated fields across table rows
          Ivan Young

          Hi Amanda,

          Welcome and I don't think there is a strong PC Police presence in the Tableau Community so shouldn't get any flaming for your data.  I'm sure this is doable in Tableau, however some insight into how your underlying data is structured will help in solving this problem.  If you could post a sample of your raw data I'm sure someone here will be happy to assist.

           

          Regards,

          Ivan

          • 2. Re: Calculated fields across table rows
            Amanda Lamb

            Thanks for your reply! Here is my dataset. I took out a few fields and left only deidentified primary keys [DA#], but otherwise all the relevant data is in there.

             

            The project I am working on is a decision point analysis of the relative rate of racial disparity through the system. So, I will be doing calculations similar to the case acceptance for disposition, if that is helpful

            • 3. Re: Calculated fields across table rows
              Ivan Young

              Hi Amanda,

              This is doable in Tableau but it's a different process than Excel.  In Excel you can reference pivot table cells outside of the pivot table which makes this sort of analysis pretty easy.   The method in Tableau is quite different.

               

              You will need to create five distinct measures then place measure names on columns and c_race on rows.  Below are the calculations you need to make.

               

              Accepted: IF [c CaseAcceptance] = 'Accepted' THEN 1 ELSE 0 END

               

              Rejected: IF [c CaseAcceptance] = 'Rejected' THEN 1 ELSE 0 END

               

              Grand Total: 1

               

              Acceptance Rate:  Sum([Accepted])/Sum([Grand Total])

               

              RRI: [Acceptance Rate]/LOOKUP([Acceptance Rate], FIRST())  (This formula divides the acceptance rate by the acceptance rate in the first row of the table.  So if you what to base RRI on whites then whites must be in the first row  or column of the tableau worksheet)

               

              Then layout the fields like you see in the screenshot below.  Let me know if you have any questions, but I won't get back to you until Monday.

               

              Good luck,

              Ivan