2 Replies Latest reply on Dec 14, 2016 12:28 PM by Daniel Price

    Calculations Using Table Values

    Daniel Price

      Hi,

       

      Is it possible to create a table that calculates from other tables? For example, I have my Grand Total Charge Off Percentages (in the picture below). For the Null years (0 through 7), I would like to apply a formula that multiplies Grand Total Charge Off Percent by Orig Bal for each year.

       

      Is this possible, and how might I go about doing that? I created the output I'm hoping to see in Excel for the example.

       

      Thank you in advance! -Dan

       

      Tableau Example.png

        • 1. Re: Calculations Using Table Values
          Chris Geatch

          Without having your data in Tableau to play with, this may not be quite right, but should give you a start.  If you can post an example file, it will make it easier.  Sorry about the dimension names, but hopefully you can work them out quite easily from the other parts of the formulae.

           

          To get the 482,861 value for the first part of you calculation, you could use:

          {EXCLUDE [ROW DIMENSION OF 0,1,2,3...]:SUM(IF [ROW DIMENSION OF ORIG BAL] = "Orig Bal" Then [MEASURE 1] ELSE 0 END)}

          This one only adds up the values from the Orig Balance, without you having to have DIMENSION OF ORIG BAL 1 in your table.  It also ignores the dimension on your rows, so you should get the same value for every row.  The value will be broken down for each year displayed.

           

          To get the 0.13% from your second table, you could use:

          {EXCLUDE [YEAR]: AVG([PERCENTAGE FIELD])}

          This one calculates the average ignoring the year field, so should give you the same value as your grand total column.  Bear in mind, though, that if you filter your years, the average will be taken over the filter, not the entire dataset (which is also possible, if you want).

           

          You can then either multiply those fields together, or put it all into one calculated field.

          1 of 1 people found this helpful
          • 2. Re: Calculations Using Table Values
            Daniel Price

            Thank you Chris! There were a couple of twists and turns but this set me on the right track for what I needed.

             

            One challenge was that I needed the calculation only to run for years that have yet to occur (2-7 for 2014, 1-7 for 2015, 0-7 for 2016), so I had to add a table calculation to get that working. I got it working. Now I just have to figure out the totals! Sounds like this is a common issue, so I'm reading some of the Grand Total walk-throughs now . Thanks again.

             

            Tableau Example2.png