2 Replies Latest reply on Apr 23, 2018 1:01 AM by Vineeth Nair

    Dividing Two Totals

    Vineeth Nair

      This is another scenario where Grand Totals make simple things seemingly impossible to calculate.

      I am trying to get the "% Delta" which is (Delta / A). This is calculated correctly for each row, except for the totals (last row in grey).

       

       

      The only options to total are SUM, AVG, MIN & MAX. And none of these are useful. Delta and %Delta are aggregated fields.

      Has anyone encountered similar trouble with Tableau and figured a way around?

        • 1. Re: Dividing Two Totals
          Joe Oppelt

          I always make a second sheet that just displays my own grand totals (which are done with my own calcs, not tableau's built-in total from "Grand total".

           

          If you have straight sums down the column, tableau adds them up nicely.  But if you have calcs in some columns, you'll find that most times Tableau doesn't do what you what it to do.

           

          So create your own window_sum calc for [A] and [B].  (Or use LOD.)  And make another [Total Delta] calc to get the accurate delta value. (Notice that your delta values seem to be absolute values, not positive- and negative-value sums.  So Tableau is arriving at the 6000 value, even though the delta between 30K and 29K is only 1000.)  And finally, make a %delta calc.  Put those on a separate sheet with the same column formatting/width/etc as your data sheet.  You'll have only one row in this sheet.

           

          Display the totals sheet below the data sheet on a dashboard.  You'll get the viz you need, and the user doesn't have to know it's two sheets.

          • 2. Re: Dividing Two Totals
            Vineeth Nair

            Hi Joe,

             

            I had tried the "Total Delta" approach (yes its an absolute, good eye), to use it in this same sheet as a different column. What I have posted as the screenshot is only a part of the bigger table and the what looks like the Total is only the sub total. So, using a different sheet to display the totals won't be very feasible. Unless there is a way to add sheets dynamically in between n number of rows.

             

            Guess I'll have to break this table down and build it up with a different structure or forget about getting the %.

             

            Thank you for replying.