2 Replies Latest reply on Sep 27, 2016 7:44 AM by Dhanashree Arole

    Displaying Table Calculation as own Column

    Kate Eberle

      Hello,

       

      I have what may be a painfully easy question, but haven't found the answer in a number of Google searches.

       

      In the attached workbook, what I'd like to do is simply display the Difference in Profit Table Calculation result as its own column to the right of the two date fields so that everything appears to be the same row.

       

      Any thoughts would be greatly appreciated.

        • 1. Re: Displaying Table Calculation as own Column
          David Li

          Hi Kate! This is definitely not a painfully easy question! Tableau is powerful in a lot of ways, but flexibility about what you display in a table layout is not one of them. To do this, we have two options;

           

          One, you can just build this in a dashboard by creating a separate sheet that calculates the difference and then putting it right next to the original one. This is the simple way to do it.

           

          Two, you can do some funky things with calculated fields to get what you want in one sheet. Take a look at the result:

           

           

          This requires two tricks: one, the knowledge that Tableau thinks of totals like normal cells, but without aggregations. That means that you can use an IF statement (in most cases) to make the total use a different calculation than the normal cells.

          IIF(COUNTD(YEAR([Order Date]))>1, SUM([Calculation2]), SUM([Profit]))

          Here, in our example, I count the distinct number of years in the aggregation. If it's the grand total, the count will be 2. Thus, when this calculation runs in the normal cells, it'll show SUM([Profit]). If it runs in the grand total, it'll show the calculation below.

           

          Next, we need to use a LOD calculation to get the difference. Note that this will only work if you have exactly 2 years filtered in, and it will only work if your date filters are gray context filters. It'd be nice if we could bypass this by using table calculations, but totals can't access the normal cells' partitions, so it's useless in this situation.

          { SUM(IIF(YEAR([Order Date]) = { MAX(YEAR([Order Date])) }, Profit, NULL)) } - { SUM(IIF(YEAR([Order Date]) = { MIN(YEAR([Order Date])) }, Profit, NULL)) }

          This may look daunting, but all it's doing is looking for all the values as of the later year, summing them up, and then subtracting the same thing for the earlier year.

           

          The last step is just to right-click the grand total header and edit the format of the grand total so that it says "Difference" in the header.

          1 of 1 people found this helpful
          • 2. Re: Displaying Table Calculation as own Column
            Dhanashree Arole

            Kate,

             

            Remember when you are learning highly advanced features in Tableau with the help of sample data, you must respect basic ground rules. Measures can be duplicated and placed neatly after you decide what goes in column and row. The duplicated measure can then be turned into quick table calculation and it is upto you to wisely use ACROSS, DOWN or other available option to solve the simple math. Overly complicating requirement is not recommended.

             

            Attached is two-step solution to what you intend to do. Feel free to modify it for deriving better insights than just getting blocked on one-sided approach.

             

            Thanks,

            Dhanashree